insert timestamp in oracle

I am trying to insert timestamp value in Oracle but its failing for the below query. Please suggest the fix for the same. I tried appending ‘timestamp’, but no luck.

insert into table (name, created_date) values ('test', '2023-04-24T11:11:11.807Z');

this is the exception I am getting:

SQL Error: ORA-01861: literal does not match format string
01861. 00000 -  "literal does not match format string"
*Cause:    Literals in the input must be the same length as literals in
           the format string (with the exception of leading whitespace).  If the
           "FX" modifier has been toggled on, the literal must match exactly,
           with no extra whitespace.
*Action:   Correct the format string to match the literal.

>Solution :

'2023-04-24T11:11:11.807Z' is not a TIMESTAMP data type, it is a string literal (that happens to look like a timestamp).

Oracle will try to be helpful and convert your string to the appropriate data type and this involves an implicit cast from string to TIMESTAMP (or TIMESTAMP WITH TIME ZONE) and your query is effectively:

insert into table (
  name,
  created_date
) values (
  'test', 
  TO_TIMESTAMP(
    '2023-04-24T11:11:11.807Z',
    (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_TIMESTAMP_FORMAT')
  )
);

However, if the NLS_TIMESTAMP_FORMAT (or NLS_TIMESTAMP_TZ_FORMAT) session parameter does not match the format of your string then you will get an exception.

What you need to do is either:

  1. Use a TIMESTAMP literal:

    insert into table (name, created_date)
values ('test', TIMESTAMP '2023-04-24 11:11:11.807 UTC');

  2. Use TO_TIMESTAMP with an explicit format model if you want to insert a TIMESTAMP:

    insert into table (name, created_date)
values (
  'test',
  TO_TIMESTAMP( '2023-04-24T11:11:11.807Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF"Z"' )
);

  3. Use TO_TIMESTAMP_TZ with an explicit format model if you want to insert a TIMESTAMP WITH TIME ZONE:

    insert into table (name, created_date)
values (
  'test',
  TO_TIMESTAMP_TZ( '2023-04-24T11:11:11.807Z', 'YYYY-MM-DD"T"HH24:MI:SS.FFTZR' )
);

fiddle

Leave a Reply