469,589 Members | 2,048 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,589 developers. It's quick & easy.

Get long date info

Bob Ross
119 100+
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
3 1060
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

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

Post your reply

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

Similar topics

5 posts views Thread by Greg | last post: by
1 post views Thread by Robert Cholewa | last post: by
2 posts views Thread by Michelle | last post: by
1 post views Thread by Robert | last post: by
9 posts views Thread by Bob Achgill | last post: by
29 posts views Thread by james | last post: by
7 posts views Thread by danielbaars | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.