Displaying the dates in a column.

Can someone help me with this. I've been trying to produce it this way but still unsuccessful.
I'd like my table to look like this:

Month: January

Day wkDay
-
1 Mon
2 Tues
3 Wed
4 Thurs
5 Fri
6 Sat
. .
. .
31 Wed

I want to display the whole month..
Please help..
Thanks..Hi,

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

select datepart(d, getdate()) 'Day',
case datepart(dw, getdate())
when 1 then 'Sun'
when 2 then 'Mon'
when 3 then 'Tue'
when 4 then 'Wed'
when 5 then 'Thu'
when 6 then 'Fri'
when 7 then 'Sat'
end 'wkDay'

BuNnY_MoOn wrote:

Can someone help me with this. I've been trying to produce it this way but still unsuccessful.
I'd like my table to look like this:

Month: January

Day wkDay
-
1 Mon
2 Tues
3 Wed
4 Thurs
5 Fri
6 Sat
. .
. .
31 Wed

I want to display the whole month..
Please help..
Thanks..

|||

you don't need a CASE statement left,3 and datename(dw) is enough

select datepart(d, getdate()) 'Day', left(datename(dw, getdate()) ,3)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks..

But i was sort of thinking of listing all the dates as per indicated in my example:

Day wkDay

1 Mon
2 Tues
3 Wed
4 Thurs
5 Fri
6 Sat
7 Sun
8 Mon
9 Tues
10 Wed
11 Thurs
12 Fri
13 Sat
14 Sun
. .
. .
31 Wed

not just one day..

|||

here you go

--first create a number table--do this only ONCE!!!!
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)

DECLARE @.intLoopCounter INT
SELECT @.intLoopCounter =0

WHILE @.intLoopCounter <=1000
BEGIN
INSERT INTO NumberPivot
VALUES (@.intLoopCounter)

SELECT @.intLoopCounter = @.intLoopCounter +1
END
GO

--now run this
SELECT datepart(dd,DATEADD(dd,numberID,GETDATE())) as Day,left(datename(dw,DATEADD(dd,numberID,GETDATE())),3) as DayName
FROM dbo.NumberPivot
WHERE NumberID < 100

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||Thanks a bunch Sql Menace! |||Create a calendar table and use it rather than writing code. It is much more flexible, robust and can handle more scenarios easily (different types of calendars - fiscal, yearly; holidays; language settings etc). Search the WWW for pointers on how to build a Calendar table.|||Thanks

Umachandar Jayachandran - MS.
I'll take that into consideration..