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 theYearFROM myTable ORDER BY theYearThis 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 myTableORDER BY DatePart(yyyy,Date) DESC,month(Date) DESCGave the required result
- March
- February
- January
- December
- November
- October