Tom J Muthirenthi Tom J Muthirenthi - 1 year ago 74
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
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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download