SQL ORDER BY date problem
SqlDateSql Order-BySql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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)
Solution 2 - Sql
It sounds to me like your column isn't a date column but a text column (varchar/nvarchar etc). You should store it in the database as a date, not a string.
If you have to store it as a string for some reason, store it in a sortable format e.g. yyyy/MM/dd.
As najmeddine shows, you could convert the column on every access, but I would try very hard not to do that. It will make the database do a lot more work - it won't be able to keep appropriate indexes etc. Whenever possible, store the data in a type appropriate to the data itself.
Solution 3 - Sql
Unsure what dbms you're using however I'd do it this way in Microsoft SQL:
select [date]
from tbemp
order by cast([date] as datetime) asc
Solution 4 - Sql
this works for me:
SELECT datefield
FROM myTable
ORDER BY CONVERT(DATE, datefield) ASC
Solution 5 - Sql
Following answer may help you
perform your date ordering by your date identifier but use to_char() function in select clause and use some other identifier in select clause for date
e.g.
SELECT TO_CHAR(DISPDATE1,'DD/MM/YYYY') AS DISPDATE,
SUM(APPLCOUNT) AS APPLIED,
SUM(CONFCOUNT) AS CONFIRMED
FROM
(
SELECT COUNT(ID) AS APPLCOUNT,
0 AS CONFCOUNT,
STUDENT.APPLIED_ON AS DISPDATE1
FROM STUDENT
WHERE STUDENT.ID = P_ID
GROUP BY STUDENT.APPLIED_ON
UNION
SELECT 0 AS APPLCOUNT,
COUNT(ID) AS CONFCOUNT,
STUDENT.CONFIRMED_ON AS DISPDATE1
FROM STUDENT
WHERE STUDENT.ID = P_ID
GROUP BY STUDENT.CONFIRMED_ON
)
GROUP BY DISPDATE1
ORDER BY DISPDATE1;
Solution 6 - Sql
SELECT CONVERT(char(19), CAST(date AS datetime), 101) as [date]
FROM tbemp
ORDER BY convert(datetime, date, 101) ASC
Solution 7 - Sql
Try using this this work for me
select * from `table_name` ORDER BY STR_TO_DATE(start_date,"%d-%m-%Y") ASC
where start_date is the field name
Solution 8 - Sql
I wanted to edit several events in descendant chonologic order, and I just made a :
select
TO_CHAR(startdate,'YYYYMMDD') dateorder,
TO_CHAR(startdate,'DD/MM/YYYY') startdate,
...
from ...
...
order by dateorder desc
and it works for me. But surely not adapted for every case... Just hope it'll help someone !
Solution 9 - Sql
This may help you in mysql, php.
//your date in any format
$date = $this->input->post('txtCouponExpiry');
$day = (int)substr($date, 3, 2);
$month = (int)substr($date, 0, 2);
$year = (int)substr($date, 7, 4);
$unixTimestamp = mktime(0, 0, 0, $year, $day, $month);
// insert it into database
'date'->$unixTimestamp;
//query for selecting order by date ASC or DESC
select * from table order_by date asc;
Solution 10 - Sql
try this
Order by Convert(datetime,@date) desc
Solution 11 - Sql
this should work for your date format
order by convert(date, your_column, 104) desc
Solution 12 - Sql
Casting/Converting can result in out of range exceptions that unfortunately are not always as simple as excluding nulls.
A simple alternative method, which avoids the cast, is:
SELECT date
FROM table
ORDER BY YEAR(date), MONTH(date), DAY(date) ASC;