Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Date sort in Oracle with TO_CHAR

Posted on 2007-10-09
4
Medium Priority
?
11,566 Views
Last Modified: 2013-12-20
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
0
Comment
Question by:aliasim99
  • 2
4 Comments
 
LVL 53

Accepted Solution

by:
_agx_ earned 1400 total points
ID: 20042477
I don't know if its possible in Oracle, but in MS SQL if you could sort by the datetime value and still return a string by giving the formatted value a different alias like this

Select someColumns,
TO_CHAR(TRACKING_START_TIME,'YYYY-MON-DD HH12:MI:SSSSS AM') as FormattedStartTimeValue
FROM
SummaryTable
Order by TRACKING_START_TIME
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 600 total points
ID: 20042730
DATE and TIMESTAMP fields are stored in the database in an internal format.  What you see when you select them are controled by the NLS_DATE_FORMAT and NLS_TIMESTAMP formats respectively.  These can be altered with an alter session command, in your case:

alter session set nls_timestamp_format = 'YYYY-MON-DD HH12:MI:SSSSS AM';

This would change the display format and eliminate the need for the TO_CHAR.  There are a couple of other ways to set it, such as in the environment on the client or in the registry (if the client is a Windows client), or it can be set at the database level in the spfile (or pfile).

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

Expert Comment

by:_agx_
ID: 20042778
>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.
0
 

Author Comment

by:aliasim99
ID: 20042993
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question