We help IT Professionals succeed at work.

Date sort in Oracle with TO_CHAR

aliasim99
aliasim99 asked
on
14,652 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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
>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.

Author

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.