developer developer - 4 months ago 186
SQL Question

getting SQL Error: ORA-01849: hour must be between 1 and 12

To one table i have to update date for column DT_MODIFIED TIMESTAMP(6) and while runing the script, iam getting below error.

update table1 set dt_modified=to_char('01Feb2016 20:04:08.000') where dt_stamp='XXXXX' and xcolumn=’YYYY’;

Error starting at line 6 in command:
update table1 set dt_modified=to_char('01Feb2016 20:04:08.000') where dt_stamp='XXXXX' and xcolumn=’YYYY’;
Error report:
SQL Error: ORA-01849: hour must be between 1 and 12
01849. 00000 - "hour must be between 1 and 12"


Please guide me on this

Answer

There were several problems with your original approach. First, since you are trying to store a date into the DT_MODIFIED column, you should be using TO_DATE or TO_TIMESTAMP, not TO_CHAR. TO_CHAR converts a date into a string, but you want to do the exact opposite of this.

Second, if you want to use 24 hour time, you need to explicitly tell Oracle this by passing in a format string. Try the following query:

UPDATE table1
SET dt_modified = TO_TIMESTAMP('01Feb2016 20:04:08.000', 'ddmonyyyy hh24:mi:ss.ff3')
WHERE dt_stamp = 'XXXXX' AND xcolumn = 'YYYY';