0

I need to display distinct values of a datetime field called datetimestamp from a SQLServer 2005 table in the MMM YYYY format. I also need them to be sorted in chronological order.

So far I have this:

Select distinct CONVERT(CHAR(4), datetimestamp, 100) + CONVERT(CHAR(4), datetimestamp, 120) as MonthYear from TableName order by MonthYear

The "order by" of course sorts it in alpha order due to the conversion.

 Apr 2009 
 Dec 2009 
 Feb 2009

How do I get it to sort it in chronological order?

Feb 2009
Apr 2009
Dec 2009

Thanks.

Damian Leszczyński - Vash
  • 30,365
  • 9
  • 60
  • 95
VVM
  • 1
  • 1
  • 1

2 Answers2

3

edit2: It looks like you'll need to pull back more than one column. This uses just the subquery from the previous attempt:

    select distinct DATEPART(year,datetimestamp) as year, 
           DATEPART(month,datetimestamp) as month,
           CONVERT(CHAR(4), datetimestamp, 100) 
           + CONVERT(CHAR(4), datetimestamp, 120) as MonthYear
    from TableName order by DATEPART(year,datetimestamp), 
                            DATEPART(month,datetimestamp)
Fosco
  • 38,138
  • 7
  • 87
  • 101
  • Thanks @Fosco. Tried your suggestion. This however breaks a SELECT DISTINCT rule. Here is the error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. – VVM Jun 06 '11 at 16:42
  • @VVM Ah yes... updated the answer with something else for you to try. – Fosco Jun 06 '11 at 17:10
  • Thanks again. Tried this one, too but got the foll. error: The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP or FOR XML is also specified. VVM – VVM Jun 06 '11 at 17:29
  • @VVM damn.. I'm making silly mistakes today... looks like you're going to need to pull more than one column. – Fosco Jun 06 '11 at 17:31
0

take a look a this post.

Get month and year from a datetime in SQL Server 2005

Community
  • 1
  • 1
JAiro
  • 5,914
  • 2
  • 22
  • 21