Tom J Muthirenthi Tom J Muthirenthi - 9 months ago 25
SQL Question

Error in timestamp datatype

When I run the below query,

select timestamp '01-01-2017 00:00' log_time from dual


I get this error


ORA-01847: day of month must be between 1 and last day of month.


If it is not valid, How to declare
timestamp
datatype in oracle?

Answer Source

If you are using ANSI timestamp literals, the value must be formatted in the ISO style yyyy-mm-dd hh24:mi:ss:

select timestamp '2017-01-01 00:00:00' log_time 
from dual;

Alternatively use Oracle's to_timestamp() function if you want to keep that format:

select to_timestamp('01-01-2017 00:00', 'dd-mm-yyyy hh24:mi') log_time 
from dual