You Asked

If you use to_date without specifying a day, Oracle assumes the 1st of the month.
However, if you don't specify a month, Oracle uses the current month.

Is this by design, and if so do you know the rationale for it?

eg on 15 September:

SQL> select to_date('2015', 'YYYY') from dual;
01-sep-2015

SQL> select to_date('2015-mar', 'YYYY-MON') from dual;
01-mar-2015

SQL> select to_date('2015-11', 'YYYY-DD') from dual;
11-sep-2015

and Connor said...

I havent seen a documentation reference that explicitly states this rule (but its been this way for as long as I've used oracle).

But it makes sense (to me :-)) to have the rules like this because you'll always get back a valid date.

If you used "current day" not "first of month" then something like:

to_date('2015-feb')

would work on January 25th but fail if you ran it on January 31st.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Andy Bowles, September 16, 2017 - 8:47 am UTC

Thanks. It surprised me because the rule is different for months than for days. I'd expect that if you miss out the month it would default to January.

(It also surprised me in another sense - I had some inadequately-tested code that worked correctly when I wrote it in January, but stopped working in February.)

September 19, 2017 - 1:54 am UTC

It also surprised me in another sense - I had some inadequately-tested code that worked correctly when I wrote it in January, but stopped working in February

One of my favourite quotes:

"Experience is what you get when you were expecting something else"

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

TO_DATE with no month

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs