SQL ORDER BY date problem

Question

Can you please help me in solving this problem. I am trying to order the results of an SQL query by date, but I'm not getting the results I need.

The query I'm using is:

SELECT date FROM tbemp ORDER BY date ASC

Results are:

01/02/2009
03/01/2009
04/06/2009
05/03/2009
06/12/2008
07/02/2009

Results should be:

06/12/2008
03/01/2009
01/02/2009
07/02/2009

I need to select the date in the format above.

Your help is much appreciated.

Answer 1

It seems that your date column is not of type datetime but varchar. You have to convert it to datetime when sorting:

select date
from tbemp
order by convert(datetime, date, 103) ASC

style 103 = dd/MM/yyyy (msdn)