• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 587
  • Last Modified:

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
0
peterseychelles
Asked:
peterseychelles
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now