asked on

Date sort in Oracle with TO_CHAR

I have Oracle 9.2.0.5 DB and Coldfusion UI.

To be exact I'm using Railo (free coldfusion server alternate).

I have a simple query

Select someColumns,
TO_CHAR(TRACKING_START_TIME,'YYYY-MON-DD HH12:MI:SSSSS AM') as TRACKING_START_TIME
From
SummaryTable
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

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

>Otherwise, you need to change the column alias. Oracle is picking the column alias for the
> sort rather than the column.

So the technique I mentioned does work in Oracle? Good to know. Thanks johnsone.

aliasim99

ASKER

Thanks for your help

You guys are right.. Alias was messing it up

Final Query:
Select someColumns,
TO_CHAR(TRACKING_START_TIME,'YYYY-MON-DD HH12:MI:SSSSS AM') as THE_START_TIME
From
SummaryTable
Order by TRACKING_START_TIME

Thanks for the explanation johnsone, I can understand what you are saying but we cant ask the client to change the default date format. The Alias fix did work.

Thanks _agx_ for a quick and good fix.

Greatly appreciated.