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.

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)

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.

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

this works for me:

SELECT datefield FROM myTable ORDER BY CONVERT(DATE, datefield) ASC

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;

SELECT CONVERT(char(19), CAST(date AS datetime), 101) as [date]
FROM tbemp ORDER BY convert(datetime, date, 101) ASC

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

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 !

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;

try this

Order by Convert(datetime,@date) desc

this should work for your date format

order by convert(date, your_column, 104) desc

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;