A general approach to sort different dateformats correctly

Wed Mar 18, 2009 by Peter Larsson in sql-server-2008, sql-server-2005, sql-server-2000

DECLARE @Stats TABLE
        (
            SomeDate DATETIME
        )

INSERT @Stats
SELECT 20000 + ABS(CHECKSUM(NEWID())) % 30000
FROM master..spt_values

DECLARE @Style INT

SET @Style = 100

WHILE @Style <= 113
    BEGIN
        -- Orders by ISO format but displays according to @Style parameter
        SELECT TOP 10 @Style AS Style,
                      CONVERT(VARCHAR(40), SomeDate, @Style) as SomeDate
        FROM          @Stats
        GROUP BY      CONVERT(VARCHAR(40), SomeDate, @Style),
                      CONVERT(VARCHAR(8), SomeDate, 112)
        ORDER BY      CONVERT(VARCHAR(8), SomeDate, 112) DESC

SET @Style = @Style + 1
END

The general idea is to group by both ISO format and the style you wish and the sort by the ISO format and display the other format.
The reason this work is that the two lines in GROUP BY clause are deterministic.

Legacy Comments


Remote DBA 2009-03-18	re: A general approach to sort different dateformats correctly very tricky approach... but works thanks, Vadym.

pavan 2009-03-18	re: A general approach to sort different dateformats correctly very neat trick.had been trying to do something along those lines this week.

Michael Valentine Jones 2010-10-28	re: A general approach to sort different dateformats correctly This seems to work OK from me without the GROUP BY. Am I missing some reason for using the GROUP BY? SELECT TOP 10 @Style AS Style, CONVERT(VARCHAR(40), SomeDate, @Style) as MyDate FROM @Stats ORDER BY SomeDate desc

Peso 2010-10-28	re: A general approach to sort different dateformats correctly The aggregation part (count) is taken out of the query.