Date sort in Oracle with TO_CHAR
Posted on 2007-10-09
I have Oracle 18.104.22.168 DB and Coldfusion UI.
To be exact I'm using Railo (free coldfusion server alternate).
I have a simple query
TO_CHAR(TRACKING_START_TIME,'YYYY-MON-DD HH12:MI:SSSSS AM') as TRACKING_START_TIME
Order by TRACKING_START_TIME
This does not work very well because of TO_CHAR and it sorts by characters rather than the actual date.
If I remove TO_CHAR and the format then it works fine on coldfusion server but in a different date format than available in DB. IT shows up like this 2007-10-08 09:45:50.0.
Column Datatype is TimeStamp(6) and the actual values in DB are in this format 21-SEP-07 08.11.07.417000000 AM
I need it in YYYY-MON-DD HH12:MI:SSSSS AM format and still be able to do sort on date by date. Any ideas?
I can use coldfusion funtions DateFormat and Timeformat but timeformat is not capable of showing sssss