Date sorting

[adamroof]

Maybe its the way its inserting the dates, but all three methods of sorting all put jun 1st in between april and may...
maybe the governments up to somethin here?

any rules on getting this sorting the right way using convert to a varchar?

DECLARE @stop int,@sql varchar(800)

--DROP TABLE #dateSort
CREATE TABLE #dateSort
	(date1 varchar(50),date2 varchar(50),date3 varchar(50))

SET @stop = 0
WHILE @stop < 10
     BEGIN
	INSERT INTO #dateSort
	SELECT 	getDate()+(5 * @stop),
		getDate()+(5 * @stop),
		getDate()+(5 * @stop)
	SET @stop = @stop + 1
     END

SELECT * FROM #dateSort
ORDER BY CONVERT(varchar(10),date1,101)

SELECT * FROM #dateSort
ORDER BY CONVERT(varchar(10),date2,102)

SELECT * FROM #dateSort
ORDER BY CONVERT(varchar(10),date3,120)

SELECT * FROM #dateSort

 

[Nagrom]

If you're only storing date values in those fields, actually create the fields as datetime data types. Then you can sort them reliably and just output the converted format (though the conversion codes 102 and 120 should be sorting them correctly since they return the data in yyyymmdd order).

 

[adamroof]

you're right, i meant to insert as smalldatetime, i changed that now all methods are sorting right, but im trying to recreate my earlier prob where it sorted by day first

 

[vongrunt]

Sort by day first happens when CONVERT() format is dmy (UK/EU and Kuwaiti styles - 103-106, 130-131).

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]