order by problem with datetime

[matt-daly]

I have datetime information for news posts stored in my database table and obviously i want the latest post to display first; so logically I order the SQL selection in descending order by the datetime column. However...

I only noticed this problem when the month moved into february. If i order the results in DESCENDING order by the datetime column i get the following results.

30/01/2006 18:42
23/01/2006 19:32
18/01/2006 02:47
02/02/2006 15:37

Obviously there is a problem with this - even though the latest post is from 2nd February, the post from the 30th of January comes first because the day is the 30th - they're ordered descendingly by the days.

Ordering them in ascending order gives the following results.

02/02/2006 15:37
18/01/2006 02:47
23/01/2006 19:32
30/01/2006 18:42

As you can see i get my latest post first, but it's still ordered by the days. This isn't any good if i make a news entry on the 3rd March (03/03)

How can i order them correctly? I've never had this problem before, but i've always formatted datestrings from within my application before inserting and stored them as type varchar. Currently these are stored as datetime objects and the formatting is done when the data is read.

Is there anyway i can order them correctly without storing them as varchars, I need to perform date formatting at runtime rather than when the record is created.

[niardica]

Are you sure the column datatype is datetime and not varchar or some other string type?

I just ran a few simple tests to show you, but if they are datetime it should definitely be ordering correctly..

create table DateTest (test_date datetime NOT NULL)
 
 insert DateTest values (convert(datetime, '01/18/2006 02:47:00'))
 insert DateTest values (convert(datetime, '01/23/2006 19:32:00'))
 insert DateTest values (convert(datetime, '02/02/2006 15:37:00'))
 insert DateTest values (convert(datetime, '01/30/2006 18:42:00'))
 
 select test_date as desc_test
 from DateTest
 order by test_date desc
 
 select test_date as asc_test
 from DateTest
 order by test_date asc
 
 drop table DateTest

--- RESULTS ----

 
 desc_test											  
 ------------------------------------------------------ 
 2006-02-02 15:37:00.000
 2006-01-30 18:42:00.000
 2006-01-23 19:32:00.000
 2006-01-18 02:47:00.000
 
 (4 row(s) affected)
 
 asc_test											   
 ------------------------------------------------------ 
 2006-01-18 02:47:00.000
 2006-01-23 19:32:00.000
 2006-01-30 18:42:00.000
 2006-02-02 15:37:00.000

[matt-daly]

ah yes you were correct. when making the post i stupidly only checked my stored procedure where i declared the variable to be entered as datetime, but sure enough i still have the table column set as a varchar.

thanks for the reply and sorry for the time.

[niardica]

No problem.. i figured it would be a simple fix