The DateName function
02 March 2008 Tips  Marek Sienkiewicz
Technorati Logo Technorati  Delicious Logo Del.icio.us  Dig Logo Digg  Blogflux Logo Blogflux
Bubble image
I wanted to include an archive list, so that a user could click a listed month and all entries for that particular month would be displayed via a Data List control. I discovered that using the DateName function I could return the specific names of the months from the DateTime value in my database table.

SELECT DISTINCT DateName(Month,Date) AS theMonth,
DatePart(Year,Date) AS theYear
FROM myTable ORDER BY theYear

This gave rise to another problem whereby the listed months were in alphabetical order and not logical order.
  • February
  • March
  • January
  • December
  • November
  • October

Rewriting the query as

SELECT DISTINCT DateName(Month,Date) AS theMonth,
DatePart(Year,Date) AS theYear,
Month(Date) AS theMonth
FROM myTable
ORDER BY DatePart(yyyy,Date) DESC,
month(Date) DESC

Gave the required result
  • March
  • February
  • January
  • December
  • November
  • October



Small Comment Icon Not really the best solution. Especially if you want to include some sort of count. I have found a better workaround. See my post for June.
Comment posted by  Marek Sienkiewicz: on  04 June 2008


Please post your comments:

Name

Email
                           

   

Enter the text you see in the box:

Captcha Image