Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL ORDER BY date problem

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.

like image 329
dnyaneshwar Avatar asked Oct 09 '09 20:10

dnyaneshwar


People also ask

Can we use ORDER BY on date in SQL?

ORDER BY is a clause in SQL which is used with SELECT query to fetch the records in ascending or descending order from a table. Just like we sort the integer and the string values stored in the column of the tables, similarly, we can sort the dates stored in the SQL table's column.

How do I sort a SQL query by date?

If you'd like to see the latest date first and the earliest date last, you need to sort in descending order. Use the DESC keyword in this case. ORDER BY ExamDate DESC ; Note that in T-SQL, NULL s are displayed first when sorting in ascending order and last when sorting in descending order.

Why ORDER BY is not working in SQL?

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

How do I order chronologically in SQL?

The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.


1 Answers

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)

like image 156
manji Avatar answered Sep 21 '22 13:09

manji