CODEWITHSUNDEEP
sqloracle-databasedatetimeora-01861
user15629883
user15629883

Reputation: 1

I can insert a datetime data

insert into "PRUEBAS_MÉDICAS" (CÓDIGO, NOMBRE, FECHA_Y_HORA, RESULTADO) 
VALUES('78426', 'TOMOGRAFÍA', '20120618 10:34:09 AM', 'Tumor Abdominal' );

And the error is

Error SQL: ORA-01861: el literal no coincide con la cadena de formato 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.

I want to insert a datetime data

Upvotes: 0

Views: 66

Answers (2)

MT0
MT0

Reputation: 168623

Either:

  1. Use TO_DATE and specify a format model:

    insert into "PRUEBAS_MÉDICAS" (
  CÓDIGO,
  NOMBRE,
  FECHA_Y_HORA,
  RESULTADO
) VALUES(
  '78426',
  'TOMOGRAFÍA',
  TO_DATE('20120618 10:34:09 AM', 'YYYYMMDD HH12:MI:SS AM'),
  'Tumor Abdominal'
);

  2. Use a TIMESTAMP literal:

    insert into "PRUEBAS_MÉDICAS" (
  CÓDIGO,
  NOMBRE,
  FECHA_Y_HORA,
  RESULTADO
) VALUES(
  '78426',
  'TOMOGRAFÍA',
  TIMESTAMP '2012-06-18 10:34:09',
  'Tumor Abdominal'
);

  3. Use DATE and INTERVAL DAY TO SECOND literals:

    insert into "PRUEBAS_MÉDICAS" (
  CÓDIGO,
  NOMBRE,
  FECHA_Y_HORA,
  RESULTADO
) VALUES(
  '78426',
  'TOMOGRAFÍA',
  DATE '2012-06-18' + INTERVAL '10:34:09' HOUR TO SECOND,
  'Tumor Abdominal'
);

  4. Change the NLS_DATE_FORMAT:

    ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD HH12:MI:SS AM';

    And use your query and rely on the implicit conversion from string-to-date to work.

    (Please don't use this last option, either use literals or explicitly specify the conversion using TO_DATE).

Upvotes: 2

rgm565
rgm565

Reputation: 66

Try:

insert into "PRUEBAS_MÉDICAS" (CÓDIGO, NOMBRE, FECHA_Y_HORA, RESULTADO)
VALUES('78426', 'TOMOGRAFÍA', TO_DATE ('20120618 10:34:09 AM', 'yyyymmdd hh:mi:ss am'), 'Tumor Abdominal' );

The error is because you haven't specified a date/time format and your default doesn't match what you have used in the values clause.

Upvotes: 2

Related Questions