A small to_date oddity

I was about to do some date/time calculations as I got an unexpected result with the to_date-function.

select to_date('12:13', 'hh24:mi'), sysdate from dual
2020-09-01 12:13:00      2020-09-28 08:59:01

As I did the to_date-conversion, based only on the hour and minute pattern, the date is set to the first of the current month. I was not expecting that. A more likely date would be todays date or a “zero” date.

The same applies when converting to timestamps:

select
  to_timestamp_tz('12:13', 'hh24:mi'),
  to_timestamp('12:13', 'hh24:mi')
from dual;

2020-09-01 12:13:00.000000000     2020-09-01 12:13:00.000000000

So – if you’re, like me, planning on doing further calculations, be sure you know what your time or actually date, that is, is converted to.

(tested on Oracle 18c)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s