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:

[code=sql]

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

[/code]

[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.