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

User · Answer

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;

User · Answer

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

User · Answer

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

User · Answer

this should work for your date format order by convert date  your column  104  desc

User · Answer

try this  Order by Convert datetime  date  desc

User · Answer

This may help you in mysql  php     your date in any format  date    this- gt input- gt 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 - gt  unixTimestamp     query for selecting  order by date ASC or DESC select   from table order by date asc

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

this works for me    SELECT datefield FROM myTable ORDER BY CONVERT DATE  datefield  ASC

User · Answer

SELECT CONVERT char 19   CAST date AS datetime   101  as  date  FROM tbemp  ORDER BY convert datetime  date  101  ASC

[sql] SQL ORDER BY date problem

Examples related to sql

Examples related to date

Examples related to sql-order-by