Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

date time order by am pm

AmivaApr 5 2014 — edited Apr 5 2014

Please help me to get expected output below. My requirement order by date am then date pm

select d from(

SELECT TO_CHAR(

         TO_DATE('15/August/2009,4:30:00 PM'

                ,'DD/Month/YYYY,HH:MI:ss AM')

       ,'DD-MM-YYYY HH:MI:ss AM') as d

FROM DUAL

union all

SELECT TO_CHAR(

         TO_DATE('15/August/2009,5:30:00 AM'

                ,'DD/Month/YYYY,HH:MI:ss AM')

       ,'DD-MM-YYYY HH:MI:ss AM')as d

FROM DUAL

union all

SELECT TO_CHAR(

         TO_DATE('15/August/2009,6:30:00 PM'

                ,'DD/Month/YYYY,HH:MI:ss AM')

       ,'DD-MM-YYYY HH:MI:ss AM')as d

FROM DUAL

union all

SELECT TO_CHAR(

         TO_DATE('15/August/2009,7:30:00 pM'

                ,'DD/Month/YYYY,HH:MI:ss AM')

       ,'DD-MM-YYYY HH:MI:ss AM')as d

FROM DUAL) order by d ;

I am getting following output for the above query in the below.

Actual output

--------------------------

15-08-2009 04:30:00 PM

15-08-2009 05:30:00 AM

15-08-2009 06:30:00 PM

15-08-2009 07:30:00 PM

How to modify the above query to get the below output. Basically i need to order by am and then  pm

expected output

--------------------------

15-08-2009 05:30:00 AM

15-08-2009 04:30:00 PM

15-08-2009 06:30:00 PM

15-08-2009 07:30:00 PM

This post has been answered by Etbin on Apr 5 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 3 2014
Added on Apr 5 2014
4 comments
998 views