CODEWITHSUNDEEP
sql-server
user3377919
user3377919

Reputation: 13

how to set order by date columns in sql server

i have a table in DB and columns like 

01-03-2013
01-04-2013
02-03-2013

i want show order by like 

01-03-2013
02-03-2013
01-04-2013

Please help me.

Upvotes: 0

Views: 432

Answers (1)

Shantanu Gupta
Shantanu Gupta

Reputation: 21108

Most probably your column is not stored as one of the date datatype this is being sorted as a string based on ASCII codes

You can cast your column for sorting.

SELECT * FROM tbl 
ORDER BY CONVERT(datetime, userColumn, 106)

otherwise it's straight forward

SELECT * FROM tbl
ORDER BY userColumn

If your column contain invalid records as well, you can use default date for such records

SELECT * FROM tbl 
ORDER BY CONVERT(datetime, 
                 CASE ISDATE(userColumn) 
                     WHEN 1 THEN userColumn 
                     ELSE '01-01-1900' 
                 END, 106)

Upvotes: 2

Related Questions