By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,700 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,700 IT Pros & Developers. It's quick & easy.

Get long date info

Bob Ross
100+
P: 119
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.
Mar 5 '08 #1
Share this Question
Share on Google+
3 Replies


amitpatel66
Expert 100+
P: 2,367
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:

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
100+
P: 119
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
100+
P: 119
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

Post your reply

Sign in to post your reply or Sign up for a free account.