Order by date doesn't work right...

[OrthoDocSoft]

Folks,

I store a date in my MS SQL Server 2005 as a date type.

My query goes something like:

"SELECT * from mytable ORDER BY [date]"

But the order I get looks like this:

8/1/2011
8/13/2011
8/2/2011
8/5/2011

(notice that 8/13 should come after 8/2 and 8/5...)

etc., so it is ordering by the "digits" (ones before twos) but not the date, if you see what I mean.

Is there an easy way to make this work?

Thank you!

Ortho

"you cain't fix 'stupid'...

 

[MarkSweetland]

My guess is that the column data type is varchar() since that is the order the data is presented. But to fix the order use:

order by convert(datetime,[date])


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[SkipVought]

REAL DATES are NUMBERS.

Your 'dates', according to the symptoms you have reported, appear to be TEXT, and as TEXT, the sort is absolutely spot on!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Olaf Doschke]

It's obvious, as the others already have seen, you're sorting by text values.

So you don't store dates in date type fields, although you think you do. SQL 2005 only knows several versions of datetime values (eg datetime, smalldatetime). From that perspective you might have choosen to use char or varchar to avoid a time portion.

That's bad both for sorting and for calculating with dates.

Bye, Olaf.