jump to navigation

What Day Is It If You Only Specify The Time? November 6, 2014

Posted by mwidlake in SQL.
Tags: ,

What is the date if you only specify the time when you populate an Oracle date column (or variable)?

That was the question that came up a few days ago in the little DBA team I am currently working in. Of course, the question was posed by the “junior” (who is damned smart at this stuff as he keeps asking questions like that) and the answer from us two old hands was… “Ohh!… hang on… errr….”

A little discussion then occurred. One of us suggested it would be “today”. One of us suggested it would be the Julian 1 date (4712BC). Both of us then stated it was an easy thing to test and the PROPER answer was “just try it and then you will know for sure”. We were right {and, of course, wrong} – as in with a simple question like this you don’t need to google the question (so “hello” to anyone googling the question and finding this page!) you just try it:

The junior tried it and…

select sysdate,to_char(to_date('11:23:15','HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') time_only
from dual;

SYSDATE              TIME_ONLY
-------------------- --------------------
05-NOV-2014 22:37:23 01-NOV-2014 11:23:15

The above of course shows that us two old hands were wrong in our suggestions of what the default date would be

The answer is that:

If you do not specify the date, it defaults to *the first day of the current month*

How odd. How amusing. What was more amusing was the two of us old hands looked at the answer and we both said “oh yes! I remember learning that before. Maybe a couple of times..”.

Maybe I am wrong and most of you reading this learned what the default date is early in your Oracle experience and never forgot it {or have learned it from here and will not forget it now, so long as you remain in possession of your wits} but both of us tired old sods laughed over the fact we knew we had known that and forgotten it. And when asked, came up with reasonable, but wrong, suggestions to the the answer. But at least we both knew it was one of those “you can answer it almost as quickly as it takes to google it” questions and the proper answer was to do a 1 minute test.

A quick check on a table in one of our applications that holds the date and time of an event in two columns (a slightly mad but common situation) demonstrated it nicely too:

select action_date,action_time
from source_table
where action_time is not null
-------------------- --------------------
09-JUN-2011 00:00:00 01-OCT-2014 11:45:30
09-DEC-2012 00:00:00 01-OCT-2014 11:12:13
09-DEC-2012 00:00:00 01-OCT-2014 17:05:57
13-JUN-2013 00:00:00 01-OCT-2014 16:25:17
17-JUN-2013 00:00:00 01-OCT-2014 16:39:00
20-JUN-2013 00:00:00 01-OCT-2014 13:00:00
25-SEP-2014 00:00:00 01-NOV-2014 08:59:00
03-NOV-2014 00:00:00 01-NOV-2014 09:00:00
03-NOV-2014 00:00:00 01-NOV-2014 00:00:00

So, if you do not specify the date, Oracle substitutes the first day of the current month. It is fully documented in the overview of the date datatype

Of course, if you do not state the time portion of a date, it defaults to the start of the current hour.

Only kidding, it of course defaults to midnight, though given how the date portion defaults my hour suggestion would almost make sense.

select sysdate,to_char(to_date('15-OCT-2013','DD-MON-YYYY'),'DD-MON-YYYY HH24:MI:SS') date_only
from dual;

SYSDATE              DATE_ONLY
-------------------- --------------------
06-NOV-14            15-OCT-2013 00:00:00

I wonder what other basic pieces of Oracle Info have left my head and if it is more or less than the average person who has been doing this for 25 years?



1. Peter Scott - November 6, 2014

And we wonder why there are all those “DATE” problems in the forums 🙂

I had one customer years back who instead of letting the time default to midnight physically set the current time to their future action dates – this made superseded actions occur just because they were committed at a later time of the day than an updated action mad a few days later for the same action date.

Sadly, it took a long time to spot this

mwidlake - November 6, 2014

Pete, the “Ingenuity” of the customer so rarely surprises me any more. But that sounds very special indeed.

Peter Scott - November 6, 2014

It was very special indeed!
It was a high profile bug that required two directors from the software vendor to sit with me pouring over trace and SQL at 3AM until I reached the “you stupid F£%^ers” moment and went home. I was not so refined in those days

2. David Grimberg - November 7, 2014

This is why I like like day to second interval types for storing time components. There’s no question of what day is defaulted in the interval is you only store time. The Day field defaults to zero. And the great thing is you can simply add it to a truncated date (or timestamp) instead of having to do any fancy date arithmetic or to_char/date conversions to get a usable value.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: