472,138 Members | 1,712 Online

# Get long date info

119 100+
I am making a table for number of logins per day of a month. Currently my results look a little like this

-------------------------------------
1 | 12
2 | 20
3 | 9

Is there any way I can get the day fo the week and relevent letters for the day so it outputs likes this -

---------------------------------------------------
Monday 1st | 12
Tuesday 2nd | 20
Wednesday 3rd | 9

Mar 5 '08 #1
3 1105
amitpatel66
2,367 Expert 2GB
I am making a table for number of logins per day of a month. Currently my results look a little like this

-------------------------------------
1 | 12
2 | 20
3 | 9

Is there any way I can get the day fo the week and relevent letters for the day so it outputs likes this -

---------------------------------------------------
Monday 1st | 12
Tuesday 2nd | 20
Wednesday 3rd | 9

Try this:

Expand|Select|Wrap|Line Numbers
1.
2. SELECT CASE DAY WHEN 1 THEN 'Monday 1st'
3.                                WHEN 2 THEN 'Tuesday 2nd'
4.                                WHEN 3 THEN 'Wednesday 3rd'
5.                                WHEN 4 THEN 'Thursday 4th'
6.                               WHEN 5 THEN 'Friday 5th'
7.                               WHEN 6 THEN 'Saturday 6th'
8.                               WHEN 7 THEN 'Sunday 7th' END, Numberoflogins FROM table1
9.
10.
Mar 5 '08 #2
Bob Ross
119 100+
That would work okay except I need the numbers to run up to 31 (for days in month) and I would rather not have a 31 case, case statement. Also I do not know if Monday is the first.

I managed to get the relevent day of the week though using:

Expand|Select|Wrap|Line Numbers
1. SELECT DATENAME(dw, CAST(@Year AS NVARCHAR)+'/'+ CAST(@Month as NVARCHAR) +'/'+ CAST(#DaysTable.DayN AS NVARCHAR)) AS 'Day',
So basically I build a string to create the correct date and then use the DATENAME(dw, xxxx) command.

Still don't know how to get the relevent letters (st, nd, rd, th, etc..). Any ideas anyone?
Mar 5 '08 #3
Bob Ross
119 100+
Well I worked out a way to do it -

Expand|Select|Wrap|Line Numbers
1. SELECT
2. CASE SUBSTRING(CAST(#DaysTable.DayN AS NVARCHAR), LEN(#DaysTable.DayN) -1, 1)
3. WHEN 1 THEN DATENAME(dw, CAST(@Year AS NVARCHAR)+'/'+ CAST(@Month as NVARCHAR) +'/'+ CAST(#DaysTable.DayN AS NVARCHAR)) + ' ' + CAST(#DaysTable.DayN AS NVARCHAR) + 'th'
4. ELSE
5.     CASE SUBSTRING(CAST(#DaysTable.DayN AS NVARCHAR), LEN(#DaysTable.DayN), 1)
6.     WHEN 1 THEN    DATENAME(dw, CAST(@Year AS NVARCHAR)+'/'+ CAST(@Month as NVARCHAR) +'/'+ CAST(#DaysTable.DayN AS NVARCHAR)) + ' ' + CAST(#DaysTable.DayN AS NVARCHAR) + 'st'
7.     WHEN 2 THEN DATENAME(dw, CAST(@Year AS NVARCHAR)+'/'+ CAST(@Month as NVARCHAR) +'/'+ CAST(#DaysTable.DayN AS NVARCHAR)) + ' ' + CAST(#DaysTable.DayN AS NVARCHAR) + 'nd'
8.     WHEN 3 THEN    DATENAME(dw, CAST(@Year AS NVARCHAR)+'/'+ CAST(@Month as NVARCHAR) +'/'+ CAST(#DaysTable.DayN AS NVARCHAR)) + ' ' + CAST(#DaysTable.DayN AS NVARCHAR) + 'rd'
9.     ELSE DATENAME(dw, CAST(@Year AS NVARCHAR)+'/'+ CAST(@Month as NVARCHAR) +'/'+ CAST(#DaysTable.DayN AS NVARCHAR)) + ' ' + CAST(#DaysTable.DayN AS NVARCHAR) + 'th'
10.     END
11. END AS 'Date'
Its certainly not an elegant solution though. I will be happy if anyone can tell me how to improve it.
Mar 5 '08 #4