Get long date info

[Bob Ross]

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

Day | Number Of Logins
-------------------------------------
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 -

Day | Number Of Logins
---------------------------------------------------
Monday 1st | 12
Tuesday 2nd | 20
Wednesday 3rd | 9

Thanks in advance for your help.

[amitpatel66]

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

Day | Number Of Logins
-------------------------------------
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 -

Day | Number Of Logins
---------------------------------------------------
Monday 1st | 12
Tuesday 2nd | 20
Wednesday 3rd | 9

Thanks in advance for your help.

Try this:

 
SELECT CASE DAY WHEN 1 THEN 'Monday 1st'
                               WHEN 2 THEN 'Tuesday 2nd'
                               WHEN 3 THEN 'Wednesday 3rd'
                               WHEN 4 THEN 'Thursday 4th'
                              WHEN 5 THEN 'Friday 5th'
                              WHEN 6 THEN 'Saturday 6th'
                              WHEN 7 THEN 'Sunday 7th' END, Numberoflogins FROM table1
 
 

[Bob Ross]

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:

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?

[Bob Ross]

Well I worked out a way to do it -

SELECT 
CASE SUBSTRING(CAST(#DaysTable.DayN AS NVARCHAR), LEN(#DaysTable.DayN) -1, 1)
WHEN 1 THEN DATENAME(dw, CAST(@Year AS NVARCHAR)+'/'+ CAST(@Month as NVARCHAR) +'/'+ CAST(#DaysTable.DayN AS NVARCHAR)) + ' ' + CAST(#DaysTable.DayN AS NVARCHAR) + 'th'
ELSE
    CASE SUBSTRING(CAST(#DaysTable.DayN AS NVARCHAR), LEN(#DaysTable.DayN), 1)
    WHEN 1 THEN    DATENAME(dw, CAST(@Year AS NVARCHAR)+'/'+ CAST(@Month as NVARCHAR) +'/'+ CAST(#DaysTable.DayN AS NVARCHAR)) + ' ' + CAST(#DaysTable.DayN AS NVARCHAR) + 'st'
    WHEN 2 THEN DATENAME(dw, CAST(@Year AS NVARCHAR)+'/'+ CAST(@Month as NVARCHAR) +'/'+ CAST(#DaysTable.DayN AS NVARCHAR)) + ' ' + CAST(#DaysTable.DayN AS NVARCHAR) + 'nd'
    WHEN 3 THEN    DATENAME(dw, CAST(@Year AS NVARCHAR)+'/'+ CAST(@Month as NVARCHAR) +'/'+ CAST(#DaysTable.DayN AS NVARCHAR)) + ' ' + CAST(#DaysTable.DayN AS NVARCHAR) + 'rd'
    ELSE DATENAME(dw, CAST(@Year AS NVARCHAR)+'/'+ CAST(@Month as NVARCHAR) +'/'+ CAST(#DaysTable.DayN AS NVARCHAR)) + ' ' + CAST(#DaysTable.DayN AS NVARCHAR) + 'th'
    END
END AS 'Date'

Its certainly not an elegant solution though. I will be happy if anyone can tell me how to improve it.