troubleshooting Question

format as date

Avatar of peterseychelles
peterseychelles asked on
Oracle DatabaseSQL
9 Comments1 Solution614 ViewsLast Modified:
I have a table called sales_header with an entry called order_date that returns a number.  I would like this number formatted as a date but using something like:
select to_date(order_date, 'DD-MON-YYYY HH24:MI:SS') as orderdate from sales_header
where sales_document_num = 33303
gives an error, presumably because to_date expects a string.  After this I tried
select to_date(to_char(order_date), 'DD-MON-YYYY HH24:MI:SS') as orderdate from sales_header
where sales_document_num = 33303
but this gives the same error (ORA-01861: literal does not match format string)
The only way I have of doign what I want at the moment is:
select to_date('01-JAN-1970 00:00:00', 'DD-MON-YYYY HH24:MI:SS') + NUMTODSINTERVAL(order_date,'SECOND') as orderdate
from sales_header
where sales_document_num = 33303
but I wanted a more elegant solution
Any ideas?  
Thanks
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros