hello hello - 2 years ago 107
SQL Question

Inserting timestamp in Oracle

I'm trying to insert the row below into Oracle database, but I get error

[22008][1830] ORA-01830: date format picture ends before converting entire input string

insert into tbl (coldate, start, end)
values (
TO_DATE('2005-03-04 02:04:30', 'YYYY-MM-DD HH24:MI:SS'),
TO_TIMESTAMP('2005-03-23 09:06:51.055000', 'YYYY-MM-DD HH24:MI:SS'),
TO_TIMESTAMP('2005-04-26 23:32:59.430000', 'YYYY-MM-DD HH24:MI:SS')

Answer Source

The error message tells you what you should do: your format mask is too "short" for the value you provide. You need to include the fractional seconds in the format mask for to_timestamp()

TO_TIMESTAMP('2005-03-23 09:06:51.055000', 'YYYY-MM-DD HH24:MI:SS.FF6')

For more details on the format model used in to_timestamp() please see the manual:


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download