What I need to do is pretty simple.
I just need to update a DATE field in SQL to a PM time.
Only thing is, if I use the TO_DATE function to update to an AM time, no problem…
TO_DATE('2021-09-30 11:00:00', 'YYYY-MM-DD HH:MI:SS')
However if I try to do the same thing to set to a PM time using military time…
TO_DATE('2021-09-30 23:00:00', 'YYYY-MM-DD HH:MI:SS')
It says that the hour has to be between 1 and 12.
Any suggestions?
Thanks
Advertisement
Answer
According to the Oracle documentation, in order to convert a time in 24 hour format, you need to use HH24. When you use just HH, Oracle assumes the time to be in AM/PM format, i.e. that the number must be between 1 and 12.
So you need to change your code to the following
TO_DATE('2021-09-30 23:00:00', 'YYYY-MM-DD HH24:MI:SS')
Refer to this db<>fiddle