format as date

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
peterseychellesAsked:
Who is Participating?
 
Shaju KumbalathDeputy General Manager - ITCommented:

select to_char(TO_DATE('19700101000000','YYYYMMDDHH24MISS')+ NUMTODSINTERVAL(1245854805, 'SECOND'),'DD-MON-YYYY HH24:MI:SS') from dual
will give u in desired format
 
shru_0409 : order date is defined in unix epoch time...
1245854805 the number indicated the number of seconds elapese since 01-january-1970 00:00:00'


 
0
 
HugoHiaslCommented:
I would try to convert it to a char and then to a date.

to_date(to_char(order_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')

0
 
peterseychellesAuthor Commented:
I had already tried that, as I said above.  Trying it exactly as you state, giving a format for the 'to_char' part gives the error 'invalid number format model'  The number returned by the order_date field is in the standard seconds since 1970 format.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
peterseychellesAuthor Commented:
Sorry, to clarify
select order_date from sales_header
where sales_document_num = 33303
returns
1245854805
and I need that in the format
'DD-MON-YYYY HH24:MI:SS'
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
it is unix ime stamp
try this
select select TO_DATE('19700101000000','YYYYMMDDHH24MISS')+ NUMTODSINTERVAL(order_date, 'SECOND')  from sales_header
where sales_document_num = 33303
 

 
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
select TO_DATE('19700101000000','YYYYMMDDHH24MISS')+ NUMTODSINTERVAL(order_date, 'SECOND') ,' from sales_header
where sales_document_num = 33303;


0
 
Shaju KumbalathDeputy General Manager - ITCommented:
sorry there is a typo
 

select select TO_DATE('19700101000000','YYYYMMDDHH24MISS')+ NUMTODSINTERVAL(order_date, 'SECOND')  from sales_header
where sales_document_num = 33303;


 
0
 
shru_0409Commented:
try this

select to_char(to_date(15301220101201,'hh24missyyyymmdd'),'DD-MON-YYYY HH24:MI:SS') f from dual

this query return 01-dec-2010 15:30:12
what is your exact date??
0
 
peterseychellesAuthor Commented:
Thanks for that.  It's a shame oracle doesn't have a function to support a direct conversion, seems like it's lacking slightly there.
Anyway, that answers my question so thanks for that
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.