Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

A general approach to sort different dateformats correctly

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.

Print | posted on Wednesday, March 18, 2009 2:31 PM | Filed Under [ SQL Server 2008 SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# re: A general approach to sort different dateformats correctly

very tricky approach... but works
thanks, Vadym.
3/18/2009 3:24 PM | Remote DBA
Gravatar

# re: A general approach to sort different dateformats correctly

very neat trick.had been trying to do something along those lines this week.
3/18/2009 4:34 PM | pavan
Gravatar

# 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
10/28/2010 10:37 PM | Michael Valentine Jones
Gravatar

# re: A general approach to sort different dateformats correctly

The aggregation part (count) is taken out of the query.
10/28/2010 11:05 PM | Peso
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET