I have a to_date function to convert a string of date and time into a date type when inserting into my table.
For almost all of the dates and times this works as I want it to, with the time component shown as below:
select to_date('22-Nov-2020 17:30:41','dd-MON-yyyy hh24:mi:ss') from dual;
Result: 22/11/2020 17:30:41
However, on the occasions that the time is exactly midnight (and this happens once each day as the data is coming from a roadside speed tracking device) then the time section is missed out of the display as below:
select to_date('22-Nov-2020 00:00:00','dd-MON-yyyy hh24:mi:ss') from dual;
Result: 22/11/2020
Can anybody explain why please? I have tried searching online but I am left scratching my head. I thought 00:00:00 was a valid time.