Wednesday, March 25, 2009

Sorting 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.

No comments: