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;