Expect The Unexpected

For years developers have been warned to not rely upon default date formats when writing application code, which is sound advice indeed. However, maybe that should be expanded to include not relying upon what one might expect when passing partial date strings to TO_DATE since the results might not match the desired outcome. Let's look at some examples and see where Oracle may not do what you think it should.

I think by now most people know that if the time is not supplied to TO_DATE it defaults to midnight:

SQL>
SQL> --
SQL> -- By default Oracle sets the time to midnight
SQL> -- if the time is not supplied to the
SQL> -- TO_DATE function
SQL> --
SQL> select TO_DATE('03012009','DDMMYYYY') from dual;

TO_DATE('03012009',
-------------------
01-03-2009 00:00:00

SQL>

so this should be expected behaviour. Let's look at what happens when the day is not supplied:

SQL> --
SQL> -- Oracle defaults to the first day of the month
SQL> -- when the day is not provided to TO_DATE
SQL> --
SQL> select TO_DATE('032009','MMYYYY') from dual;

TO_DATE('032009','M
-------------------
03-01-2009 00:00:00

SQL>

So far this is behaviour to be expected. What happens when only the year is supplied? Let's find out:

SQL> --
SQL> -- Oracle defaults to the first day
SQL> -- of the current month when neither month nor day
SQL> -- is specified
SQL> --
SQL> select TO_DATE('1995','YYYY') from dual;

TO_DATE('1995','YYY
-------------------
03-01-1995 00:00:00

SQL>

Hmmm, I would have expected the current month and day to be returned. Now let's pass the day and the year to TO_DATE:

SQL> --
SQL> -- Oracle defaults to the current month
SQL> -- when it is not provided
SQL> --
SQL> -- We illustrate this by passing the day and the
SQL> -- year to TO_DATE
SQL> --
SQL> select TO_DATE('092008','DDYYYY') from dual;

TO_DATE('092008','D
-------------------
03-09-2008 00:00:00

SQL>

And that's what I would expect. Passing in the day and the month causes Oracle to use the current year:

SQL> --
SQL> -- The default is to assume the current year if it
SQL> -- is not provided
SQL> --
SQL> select TO_DATE('0712','MMDD') from dual;

TO_DATE('0712','MMD
-------------------
07-12-2009 00:00:00

SQL>

and that's, again, what I would expect. Now let's pass in only the day:

SQL> --
SQL> -- Assume the current month and year when only
SQL> -- the day is provided
SQL> --
SQL> select TO_DATE('23','DD') from dual;

TO_DATE('23','DD')
-------------------
03-23-2009 00:00:00

SQL>

Now let's provide only the time:

SQL> --
SQL> -- Strangeness abounds, as when only the time is provided
SQL> -- Oracle defaults to the current month but assumes
SQL> -- the first day of that month
SQL> --
SQL> select to_date('11:00:00','hh24:mi:ss')
  2  from dual;

TO_DATE('11:00:00',
-------------------
03-01-2009 11:00:00

SQL>

I would have thought Oracle would presume the current month and day for that example, but what I think and what Oracle does are two different things.

So, Oracle can do the expected, depending upon which part of the date string is supplied. It can also do the unexpected, and that can be unnerving if you're trying to troubleshoot an application and can't understand why the date arithmetic is off:

SQL> --
SQL> -- This doesn't return the expected result
SQL> -- because Oracle assumes the first day of the
SQL> -- month, not the current day
SQL> --
SQL> select sysdate - to_date('07:00:00','hh24:mi:ss')
  2  from dual;

SYSDATE-TO_DATE('07:00:00','HH24:MI:SS')
----------------------------------------
                              4.08762731

SQL>

Fixing that problem means rewriting the query a bit:

SQL> --
SQL> -- Let's get the result we expected
SQL> --
SQL> select sysdate - to_date(to_char(sysdate, 'MM-DD-RRRR')||' 07:00:00','mm-dd-rrrr hh24:mi:ss')
  2  from dual;

SYSDATE-TO_DATE(TO_CHAR(SYSDATE,'MM-DD-RRRR')||'07:00:00','MM-DD-RRRRHH24:MI:SS'
--------------------------------------------------------------------------------
                                                                      .087627315

SQL>

If you're not certain what Oracle will return from a function call you should test the code before assuming anything as the examples above attest. We certainly didn't get the second result from the code in the prior example simply because Oracle didn't return the default data as we thought it should.

Expect the unexpected, and nothing should be a surprise. Well, at least not an unpleasant one.