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.

That Was The Week That Wasn’t

The following question seems simple enough:

Is there a quick way to convert week number (of the year) to start date of that week?

EX: If I pass week number as 1 then it should return 1/1/2009
    If I pass week number as 7 then it should return 2/8/2009

This, of course, assumes a number of things which may or may not be true, such as the first day of week 1 is January 1 and that week 7 is defined to include, and also begin on, February 8. Such assumptions depend upon which week numbering ‘scheme’ one elects to use, and there are two common schemes currently in use by Oracle. Let’s look at both of them and see what differences they contain and how they can throw the listed assumptions ‘out of the window’.

The two week numbering systems in use by Oracle are the U.S. week numbering system and the ISO week numbering system. They ARE different in how they define Week number 1 and that can throw a ‘monkey wrench’ into any methodology one could implement to answer the above listed question.

If we use the U.S week numbering system we can easily satisfy the first condition listed in the posted question as Week 1 is defined as the week containing January 1:

SQL> with date_wk as (
  2        select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt
  3        from dual
  4        connect by level <= 366
  5  ),
  6  wk_dt as (
  7        select dt, to_number(to_char(dt, 'ww')) wk_of_yr
  8        from date_wk
  9  )
 10  select dt, wk_of_yr
 11  from wk_dt
 12  where wk_of_yr = &&1
 13  /

DT          WK_OF_YR
--------- ----------
01-JAN-09          1
02-JAN-09          1
03-JAN-09          1
04-JAN-09          1
05-JAN-09          1
06-JAN-09          1
07-JAN-09          1
01-JAN-10          1

8 rows selected.

SQL>

[In Oracle syntax the format specifier for the U.S. week numbering system is WW, in either upper or lower case. The first subquery shown generates a list of dates starting with January 1 of the current year and ends 365 days later. The second subquery takes that list and generates the U.S. week number for each date. The final query returns results based upon the supplied week number.]

But Week 7 of that numbering convention doesn’t contain February 8, 2009:

SQL> with date_wk as (
  2          select to_date('01/01/&&1', 'MM/DD/RRRR') + rownum - 4 dt
  3          from dual
  4          connect by level <= 366
  5  ),
  6  wk_dt as (
  7          select dt, to_number(to_char(dt, 'ww')) wk_of_yr
  8          from date_wk
  9  )
 10  select dt, wk_of_yr
 11  from wk_dt
 12  where wk_of_yr = &&2
 13  and rownum <= 7
 14  /

DT          WK_OF_YR
--------- ----------
12-FEB-09          7
13-FEB-09          7
14-FEB-09          7
15-FEB-09          7
16-FEB-09          7
17-FEB-09          7
18-FEB-09          7

7 rows selected.

SQL>
SQL> with date_wk as (
  2          select to_date('01/01/&&1', 'MM/DD/RRRR') + rownum - 4 dt
  3          from dual
  4          connect by level <= 366
  5  ),
  6  wk_dt as (
  7          select dt, to_number(to_char(dt, 'ww')) wk_of_yr
  8          from date_wk
  9  )
 10  select min(dt)
 11  from wk_dt
 12  where wk_of_yr = &&2
 13  and rownum <= 7
 14  /

MIN(DT)
---------
12-FEB-09  

SQL>

Week 6 does, although it’s not the starting date of that week:

SQL> with date_wk as (
  2        select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt
  3        from dual
  4        connect by level <= 366
  5  ),
  6  wk_dt as (
  7        select dt, to_number(to_char(dt, 'ww')) wk_of_yr
  8        from date_wk
  9  )
 10  select dt, wk_of_yr
 11  from wk_dt
 12  where wk_of_yr = &&1
 13  /

DT          WK_OF_YR
--------- ----------
05-FEB-09          6
06-FEB-09          6
07-FEB-09          6
08-FEB-09          6
09-FEB-09          6
10-FEB-09          6
11-FEB-09          6

7 rows selected.

SQL>

Now, if the ISO week numbering convention is used the first condition of the question won’t be satisfied as Week 1 is defined to contain the first Thursday of the calendar year, thus the starting date for ISO Week 1 can be in December, and for 2009 it is:

SQL> with date_wk as (
  2        select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 4 dt
  3        from dual
  4        connect by level <= 366
  5  ),
  6  wk_dt as (
  7        select dt, to_number(to_char(dt, 'iw')) wk_of_yr
  8        from date_wk
  9  )
 10  select dt, wk_of_yr
 11  from wk_dt
 12  where wk_of_yr = &&1
 13  /

DT          WK_OF_YR
--------- ----------
29-DEC-08          1
30-DEC-08          1
31-DEC-08          1
01-JAN-09          1
02-JAN-09          1
03-JAN-09          1
04-JAN-09          1

7 rows selected.

SQL>

[The Oracle format specifier for the ISO week numbering system is IW, in either upper or lower case. The change to the format specifier is the only change made to the query posted at the beginning.]

ISO Week 7 doesn’t answer the second condition, either, since February 8, 2009 is the last day of ISO Week 6:

SQL> with date_wk as (
  2        select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 4 dt
  3        from dual
  4        connect by level <=366
  5 )
  6  wk_dt as (
  7        select dt, to_number(to_char(dt, 'iw')) wk_of_yr
  8        from date_wk
  9  )
 10  select dt, wk_of_yr
 11  from wk_dt
 12  where wk_of_yr = &&1
 13  /

DT          WK_OF_YR
--------- ----------
02-FEB-09          6
03-FEB-09          6
04-FEB-09          6
05-FEB-09          6
06-FEB-09          6
07-FEB-09          6
08-FEB-09          6

7 rows selected.

SQL>
SQL> with date_wk as (
  2          select to_date('01/01/&&1', 'MM/DD/RRRR') + rownum - 4 dt
  3          from dual
  4          connect by level <= 366
  5  ),
  6  wk_dt as (
  7          select dt, to_number(to_char(dt, 'iw')) wk_of_yr
  8          from date_wk
  9  )
 10  select min(dt)
 11  from wk_dt
 12  where wk_of_yr = &&2
 13  and rownum <= 7
 14  /

MIN(DT)
---------
02-FEB-09  

SQL>

How, then, is the ISO week defined? It starts on Monday and ends on Sunday, and ISO Week 1 is defined in the following equivalent terms:

the week with the year’s first Thursday in it (the ISO 8601 definition)
the week starting with the Monday which is nearest in time to 1 January
the week with the year’s first working day in it (if Saturdays, Sundays, and 1 January are not working days)
the week with January 4 in it
the first week with the majority (four or more) of its days in the starting year
the week starting with the Monday in the period 29 December – 4 January
the week with the Thursday in the period 1 – 7 January
If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53 of the previous year.

Given the above definition there are some years where even the first condition of the original question won’t be satisfied, like 2010, where the first day of ISO Week 1 is January 4:

SQL> SQL> with date_wk as (
  2          select to_date('01/01/&&1', 'MM/DD/RRRR') + rownum - 4 dt
  3          from dual
  4          connect by level <= 366
  5  ),
  6  wk_dt as (
  7          select dt, to_number(to_char(dt, 'iw')) wk_of_yr
  8          from date_wk
  9  )
 10  select dt, wk_of_yr
 11  from wk_dt
 12  where wk_of_yr = &&2
 13  and rownum <= 7
 14  /

DT          WK_OF_YR
--------- ----------
04-JAN-10          1
05-JAN-10          1
06-JAN-10          1
07-JAN-10          1
08-JAN-10          1
09-JAN-10          1
10-JAN-10          1

7 rows selected.

SQL>
SQL> with date_wk as (
  2          select to_date('01/01/&&1', 'MM/DD/RRRR') + rownum - 4 dt
  3          from dual
  4          connect by level <= 366
  5  ),
  6  wk_dt as (
  7          select dt, to_number(to_char(dt, 'iw')) wk_of_yr
  8          from date_wk
  9  )
 10  select min(dt)
 11  from wk_dt
 12  where wk_of_yr = &&2
 13  and rownum <= 7
 14  /

MIN(DT)
---------
04-JAN-10  

SQL>

The U.S. week is defined as starting on Sunday and ending on Saturday. Week number 1 in this convention is defined as the week beginning on January 1, which may be a “partial” week based on the convention that calendar weeks start on Sunday and end on Saturday. As such the last week of the year in this convention can also be a “partial” week based on the convention stated in the previous sentence. The full definition of U.S. Week Number 1 is:

The first week of the year contains 1 January, the 1st Saturday and is comprised of days 1-7 of the year.

This allows the first week of the year to start on any day of the conventional week and end six days later; the first week could run from Wednesday to Tuesday rather than from Sunday to Saturday. So, the question, as posed, relies upon a numbering system which allows partial weeks, the weeks always start on Sunday (so how does a partial week occur?), always end on Saturday and declare Week Number 1 as that week starting with January 1 (a criteria that can run afoul of the Sunday to Saturday, 7 days in a week ‘rule’). In such a system February 8, 2009, would be the starting date for Week 7 (because Week 1 only has three days, January 1,2 and 3, a strange occurrence indeed as it contradicts the stated definition of every week starting on a Sunday). I don’t know of a numbering scheme which meets that conflicting criteria. But, there MIGHT be one in use somewhere which satisfies all of those conditions. Stranger things have happened, but with the given numbering schemes available the answer to the original question is “No”.

And that was the week that wasn’t.

What do Julius Caesar, Jacques Cassini and Pope Gregory XIII have in common? Oracle!

“Oh, what a tangled web we weave, …”

Sir Walter Scott

Julian date numbers prior to AD 1 can be, well, a mess in Oracle. Take, for example, the stated date limit imposed by the database, which is January 1, 4712 BC. According to Oracle this is the start date for the Julian calendar … but it isn’t, as the start date is actually one year earlier.

Huh?!?!?

It’s true, and to explain this we need to delve into several calendars and conventions to see exactly how this came to be. We need to define a term, first, and that term is proleptic. This means to extend a calendar past the date of its inception to extrapolate dates (dates that didn’t exist in that calendar because it didn’t exist yet or it wasn’t stable). The year the Julian calendar became reliable is 4 AD, which is when the leap years stabilized [old Julius Caesar may have been a good ruler, but his math skills were apparently lacking]. Any ‘Julian’ date prior to 4 AD is considered a proleptic Julian date. And more on that later.

Julian Date Numbers (or Julian dates, as they are commonly considered) are based upon this proleptic calendar as they start at an epoch defined as January 1, 4713 BC, which is Julian Day Number 0 (since 4713 BC is WAY earlier than 4 AD [our stable starting point for the Julian calendar] this is in the proleptic Julian calendar and is, in fact, the considered ‘starting’ date for that calendar chosen because all three cycles of the Julian calendar [indiction {15-year cycle}, Metonic {roughly, a lunar cycle} and Solar] coincided for the first time). And that’s just fine, unless you run afoul of a couple of ‘gotchas‘. Those ‘gotchas’ are:

The Year Zero Rule

With the common BC/AD convention there is no Year 0, as 1 BC directly precedes 1 AD. Not so with an astronomical numbering convention, which designates years with a +/- scheme and includes a year 0 [this convention was first used by French astronomer Jacques Cassini in the mid-18th century]; the ‘AD’ years remain as-is, but the ‘BC’ end of the scale changes thusly:

2 BC is notated as -1
1 BC is notated as 0

So NOW there IS a year 0, but, well, Oracle doesn’t quite know what to do with it. And this is not the only complication, as

The inception of the Gregorian calendar

resulted in a loss of at least 10 days to correct for an excess of accumulated days due to the original, erroneous leap year rule [and we can’t thank Pope Gregory XIII enough for that]. Which 10+ days depends upon the date of adoption:

If the adoption date was October 15, 1582
—————-> then October 5 through 14 vanish.
If the adoption date was September 14, 1752
—————-> then September 3 through 13 disappear.

And there’s an option of no cutover/adoption date at all, in which the dates are considered either all Julian (going back to our proleptic Julian calendar) or all Gregorian (which establishes a proleptic Gregorian calendar) but preserves the ‘missing’ dates for eternity. [There are also other adoption dates for the Gregorian calendar throughout Europe; the two listed are the most ‘popular’.] Oracle chose to use October, 1582 as its cutover date. UNIX, on the other hand, uses the September, 1752 cutover.

So, why is Oracle unable to handle the year 0? It appears that somewhere in the development cycle a programmer decided to use the astronomical convention to calculate the Julian Day Numbers but use BC/AD notation without a correct ‘translation’. Thus the astronomical year -4712 (4712 years before year 0) becomes, in Oracle, 4712 BC instead of its actual proleptic Julian year of 4713 BC. So notation of all of the BC years in Oracle is off by 1, due to the Year 0 issue. The calculations, though, are correct, for any date on or after March 1, 4713 BC (-4712/03/01 using the astronomical notation). Why March 1, 4713 BC? Oracle considers January 1, 4713 BC as Julian Day Number 1 but it’s really Julian Day Number 0 (as mentioned previously). But, the calculation of the Julian Day Number for that date returns … 1:

SQL> with jdna as (
 2      select floor((14 - 1)/12) a
 3      from dual
 4 ),
 5 jdny as (
 6     select -4712 + 4800 - a y
 7     from dual, jdna
 8 ),
 9 jdnm as (
10      select 1 + (12*a) - 3 m
11      from dual, jdna
12 )
13 select 1 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian
14 from jdna, jdnm, jdny, dual;

    JULIAN
----------
      1.15

SQL>

[Maybe Julian Day 0 occurs at exactly noon on January 1, 4713 BC, meaning 12:01 PM on that same day would start Julian Day 1. Or not.] And, since Year 4713 BC would be a leap year (because AD 4 is a leap year that makes year AD 0 [ 1 BC] a leap year AND since AD -4712 is evenly divisible by 4 that makes IT [4713 BC] a leap year) it would have a February 29:

SQL> with jdna as (
 2      select floor((14 - 2)/12) a
 3      from dual
 4 ),
 5 jdny as (
 6      select -4712 + 4800 - a y
 7      from dual, jdna
 8 ),
 9 jdnm as (
10      select 2 + (12*a) - 3 m
11      from dual, jdna
12 )
13 select 29 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian
14 from jdna, jdnm, jdny, dual;

    JULIAN
----------
     59.75

SQL>

but Oracle doesn’t know that date exists, so the errors cancel each other by making March 1, 4713 BC Julian Day Number 60, which is correct. [Apparently Oracle is rounding the results because if we use the formula to calculate the Julian Day Number for March 1, 4713 BC we get:

SQL> with jdna as (
 2      select floor((14 - 3)/12) a
 3      from dual
 4 ),
 5 jdny as (
 6      select -4712 + 4800 - a y
 7      from dual, jdna
 8 ),
 9 jdnm as (
10      select 3 + (12*a) - 3 m
11      from dual, jdna
12 )
13 select 1 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian
14 from jdna, jdnm, jdny, dual;

    JULIAN
----------
      60.4

SQL>

Round that calculation and you get 60; round the calculation for February 29, 4713 BC and you get … 60. This may be why Oracle refuses to accept
-4712/02/29 as a valid date:

SQL> select to_date('-4712/02/29','syyyy/mm/dd') from dual;
select to_date('-4712/02/29','syyyy/mm/dd') from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified


SQL>

(two dates cannot possess the same Julian Day Number as they are unique).] Any Julian Day Number from March 1, 4713 BC onward is correct. To prove this let’s generate the rounded, truncated and actual Julian day numbers for the date range of 01/01/4713 BC to 03/01/4713 BC and see what ‘falls out’:

SQL> with jd as (
  2  SELECT to_number(to_char(daterange, 'MM')) Mth,
  3         to_number(to_char(daterange, 'DD')) D,
  4         to_number(to_char(daterange, 'SYYYY')) YR
  5  FROM
  6  (
  7    select to_date('01 -4712','MM SYYYY') + (level - 1) as DateRange
  8    from    dual
  9    where   (to_date('01 -4712','MM SYYYY')+level-1) <= last_day(sysdate)
 10    connect by level<=60
 11  )
 12  union
 13  select 2, 29, -4712 from dual
 14  ORDER BY 1,2,3
 15  ),
 16  jdna as (
 17     select floor((14 - mth)/12) a, mth mh, d dy, yr yy
 18     from jd
 19  ),
 20  jdny as (
 21     select yy + 4800 - a y, mh mn, dy dn, yy yn
 22     from jdna
 23  ),
 24  jdnm as (
 25     select mh + (12*a) - 3 m, mh mm, dy dm, yy ym
 26     from jdna
 27  )
 28  select  mh, dy,
 29         case when yy< 1 then abs(1 - yy) else yy end yr,
 30         case when yy < 1 then 'BC' else 'AD' end era,
 31         dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian,
 32         round(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0) rnd_julian,
 33         trunc(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083) trunc_julian,
 34         case when mh < 3 and yy < 1 then
 35              round(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0)-1
 36         else
 37              round(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0)
 38         end actual_julian
 39  from jdna, jdnm, jdny
 40  where jdnm.mm = jdna.mh
 41  and jdny.mn = jdna.mh
 42  and jdnm.dm = jdna.dy
 43* and jdny.dn = jdna.dy

        MO         DY         YR ER     JULIAN RND_JULIAN TRUNC_JULIAN ACTUAL_JULIAN
---------- ---------- ---------- -- ---------- ---------- ------------ -------------
         1          1       4713 BC       1.15          1            1             0
         1          2       4713 BC       2.15          2            2             1
         1          3       4713 BC       3.15          3            3             2
         1          4       4713 BC       4.15          4            4             3
         1          5       4713 BC       5.15          5            5             4
         1          6       4713 BC       6.15          6            6             5
         1          7       4713 BC       7.15          7            7             6
         1          8       4713 BC       8.15          8            8             7
         1          9       4713 BC       9.15          9            9             8
         1         10       4713 BC      10.15         10           10             9
         1         11       4713 BC      11.15         11           11            10
         1         12       4713 BC      12.15         12           12            11
         1         13       4713 BC      13.15         13           13            12
         1         14       4713 BC      14.15         14           14            13
         1         15       4713 BC      15.15         15           15            14
         1         16       4713 BC      16.15         16           16            15
         1         17       4713 BC      17.15         17           17            16
         1         18       4713 BC      18.15         18           18            17
         1         19       4713 BC      19.15         19           19            18
         1         20       4713 BC      20.15         20           20            19
         1         21       4713 BC      21.15         21           21            20
         1         22       4713 BC      22.15         22           22            21
         1         23       4713 BC      23.15         23           23            22
         1         24       4713 BC      24.15         24           24            23
         1         25       4713 BC      25.15         25           25            24
         1         26       4713 BC      26.15         26           26            25
         1         27       4713 BC      27.15         27           27            26
         1         28       4713 BC      28.15         28           28            27
         1         29       4713 BC      29.15         29           29            28
         1         30       4713 BC      30.15         30           30            29
         1         31       4713 BC      31.15         31           31            30
         2          1       4713 BC      31.75         32           31            31
         2          2       4713 BC      32.75         33           32            32
         2          3       4713 BC      33.75         34           33            33
         2          4       4713 BC      34.75         35           34            34
         2          5       4713 BC      35.75         36           35            35
         2          6       4713 BC      36.75         37           36            36
         2          7       4713 BC      37.75         38           37            37
         2          8       4713 BC      38.75         39           38            38
         2          9       4713 BC      39.75         40           39            39
         2         10       4713 BC      40.75         41           40            40
         2         11       4713 BC      41.75         42           41            41
         2         12       4713 BC      42.75         43           42            42
         2         13       4713 BC      43.75         44           43            43
         2         14       4713 BC      44.75         45           44            44
         2         15       4713 BC      45.75         46           45            45
         2         16       4713 BC      46.75         47           46            46
         2         17       4713 BC      47.75         48           47            47
         2         18       4713 BC      48.75         49           48            48
         2         19       4713 BC      49.75         50           49            49
         2         20       4713 BC      50.75         51           50            50
         2         21       4713 BC      51.75         52           51            51
         2         22       4713 BC      52.75         53           52            52
         2         23       4713 BC      53.75         54           53            53
         2         24       4713 BC      54.75         55           54            54
         2         25       4713 BC      55.75         56           55            55
         2         26       4713 BC      56.75         57           56            56
         2         27       4713 BC      57.75         58           57            57
         2         28       4713 BC      58.75         59           58            58
         2         29       4713 BC      59.75         60           59            59
         3          1       4713 BC      60.4          60           60            60


SQL>

Notice if the calculation is rounded then Februrary 29, 4713 BC has the same Julian Day Number as March 1, 4713 BC (as noted earlier); if the calculation is truncated then February 1, 4713 BC has the same Julian Day Number as January 31, 4713 BC and, well, we can’t have a calendar where February 1 doesn’t exist thus the reason [I suspect] that Oracle chose to round the value and then discard February 29, 4713 BC. But, had Oracle performed the numeric ‘gyrations’ illustrated in the query above we could have had proper Julian Day numbers and all of the actual proleptic dates of 4713 BC would exist.

[The above query works on Oracle releases 10.2.x and later due to the ‘connect by level<=60' statement, which produces a date list of 60 values used to generate the posted table. In releases 9.x that statement will parse and execute but may not provide any more than one row of output; in 8.x and earlier that statement will produce a syntax error.]

(Notice that the query uses the “&&” syntax for positional variables; this allows the variable to be set once, when the script is called, and passes the set value through the script to any other places where it is used. Using a single “&” would cause SQL*Plus to request the value for each occurrence in the statement. The up side to this is that variables need only be supplied values once during a run; the down side is the values are persistent between runs and either need to be submitted again on the command line [for a script] or need an explicit ‘undefine’ statement to clear them [as in ‘undefine 1’, for example, to ‘clear’ the value for &&1]. The double & can also be used on named variables, such as &&my_tbl; the undefine syntax works on these, as well, so that ‘undefine my_tbl’ will clear any set value supplied so subsequent runs will request a new value.)

And this gets better. Oracle may not recognize 1 BC as year 0 but the calculations do. Ask Oracle for the number of days difference between December 31, 1 BC and January 1, 1 AD:

SQL> select to_date('01/01/0001', 'mm/dd/yyyy') - to_date('12/31/0001 BC', 'mm/dd/yyyy bc') "This Should Be 1"
 2 from dual;

This Should Be 1
----------------
             367

SQL>

Now let’s ask Oracle to add 1 to the date December 31, 1 BC:

SQL> select to_date('31-DEC-0001 BC', 'DD-MON-YYYY AD')+1 from dual
 2 /

TO_DATE('31
-----------
01-JAN-0000

SQL>

So Year 0 exists (at least according to the JDN formula and an interesting date addition illustrated above) and it’s a leap year. Obviously Oracle takes the 1 BC value and simply tacks on a minus sign, making the year -1 in the astronomical convention — which is wrong as discussed previously. Were Oracle to do this correctly 1 BC would become Year 0 and the returned value for the above query would be 1:

SQL> with jdna as (
 2      select floor((14 - &&1)/12) a
 3      from dual
 4 ),
 5 jdny as (
 6      select &&3 + 4800 - a y
 7      from dual, jdna
 8 ),
 9 jdnm as (
10      select &&1 + (12*a) - 3 m
11      from dual, jdna
12 )
13 select &&1 mo, &&2 dy,
14        case when &&3  with jdna as (
 2      select floor((14 - &&1)/12) a
 3      from dual
 4 ),
 5 jdny as (
 6      select &&3 + 4800 - a y
 7      from dual, jdna
 8 ),
 9 jdnm as (
10      select &&1 + (12*a) - 3 m
11      from dual, jdna
12 )
13 select &&1 mo, &&2 dy,
14        case when &&3  select &after_jc - &before_jc from dual;

1721424-1721423
---------------
              1

SQL>

(Oracle recognizes this as a bug (106242) but, due to possible backwards compatibility issues, hasn’t fixed it.)

Can you fix the display issue in Oracle? Well, sort of — you’ll need to generate the date string, parse it, then, if it’s a BC date add one more to the year:

SQL> declare
 2      yr number;
 3      bc varchar2(2);
 4      mo varchar2(10);
 5      dy varchar2(2);
 6
 7 begin
 8      select to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'AD'),
 9             to_number(to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'YYYY')),
10             to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'MONTH'),
11             to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'DD')
12      into bc, yr, mo, dy
13      from dual;
14
15      if bc = 'BC' then
16           yr := yr + 1;
17      end if;
18
19      dbms_output.put_line('The date submitted is '||mo||' '||dy||', '||yr||' '||bc);
20
21 end;
22 /
The date submitted is JANUARY 01, 4713 BC

PL/SQL procedure successfully completed.

SQL>

It may be easier to simply remember the BC date notation is one year off.

But, hey, the Julian Date Numbers are correct … if you don’t count January and February of 4713 BC … so is Oracle wrong or is the formula wrong? It appears a bit of both, really, as the formula (as proven) returns the wrong values for dates from the epoch through February 29, 4713 BC, and Oracle doesn’t ‘translate’ the astronomical numbering convention correctly, associating -1 with 1 BC. When it rains, it pours.

Now, where did I put that leap year?

How About A Date?

It seems that DATE fields in an Oracle database can be rather confusing, especially when attempting to extract various portions of the date string. Enter the TO_CHAR() function:

TO_CHAR({source}, [format])

where {source}

is usually a database column. The various pieces you can use to build your [format] can be found here. DATE columns contain both the date and time in a proprietary Oracle structure, and either or both can be extracted and displayed in various formats. To extract just the date from a DATE field and display it as a string you can write this:

TO_CHAR(mydate, 'DD-MON-YYYY')

or this:

TO_CHAR(mydate, 'Month DDth, YYYY')

or this:

TO_CHAR(mydate, 'MM/DD/YY')

or any of a number of other combinations. As expected the time portion can also be extracted and displayed by itself:

TO_CHAR(mydate, 'HH24:MI:SS')

to return time in a 24-hour, military format or in a ‘standard’ 12-hour format:

TO_CHAR(mydate, 'HH:MI:SS AM')

which will append either AM or PM to the time string. You can also use these same format masks with the TO_DATE function, converting character strings into valid Oracle date values:

TO_DATE('12:36:54 PM', 'HH:MI:SS AM')

although just supplying a time to the TO_DATE function will, by default, set the actual date to January 1, 1900 (probably not what you had intended). TO_DATE works best with either date strings or date/time strings; in the absence of a time value TO_DATE will set that portion of the date value to midnight (00:00:00 in 24-hour format):

SQL> create table datetst(mydate date);

Table created.

SQL> --
SQL> -- Inserts current date and time
SQL> --
SQL> insert into datetst values (sysdate);

1 row created.

SQL> --
SQL> -- Sets the time portion to midnight
SQL> --
SQL> insert into datetst values(to_date('05/30/08','MM/DD/YY'));

1 row created.

SQL> select to_char(mydate, 'DD-MON-YYYY HH24:MI:SS') from datetst;

TO_CHAR(MYDATE,'DD-M
--------------------
03-JUN-2008 07:49:12
30-MAY-2008 00:00:00

SQL>

You may have noticed the ‘shorthand’ Oracle uses for the current date and time: SYSDATE This allows you to select the current date and time (as set on the database server) whenever you want it. SYSDATE is called a pseudo-column, one that exists in the absence of any table, meaning you can select SYSDATE from any table:

SQL> select sysdate from datetst;

SYSDATE
---------
03-JUN-08
03-JUN-08

SQL> select sysdate from dual;

SYSDATE
---------
03-JUN-08

SQL>

Pay close attention to the example above, as selecting SYSDATE from a table having more than one row can produce a LOT of output you probably hadn’t planned to see. Pseudo-columns return their value once for each row in the source table or in the result set (when restrictions are in place). Selecting SYSDATE from, say, the USER_OBJECTS table can generate a long list of values:

SQL> select sysdate from user_objects

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08
03-JUN-08

SYSDATE
---------
03-JUN-08
03-JUN-08

123 rows selected.

SQL>

And I guess now is as good of a time as any to introduce another pseudo-column, ROWNUM. ROWNUM is assigned to each row of a result set, starting with 1:

SQL> select rownum from user_objects;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11

    ROWNUM
----------
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22

    ROWNUM
----------
        23
        24
        25
        26
        27
        28
        29
        30
        31
        32
        33

    ROWNUM
----------
        34
        35
        36
        37
        38
        39
        40
        41
        42
        43
        44

    ROWNUM
----------
        45
        46
        47
        48
        49
        50
        51
        52
        53
        54
        55

    ROWNUM
----------
        56
        57
        58
        59
        60
        61
        62
        63
        64
        65
        66

    ROWNUM
----------
        67
        68
        69
        70
        71
        72
        73
        74
        75
        76
        77

    ROWNUM
----------
        78
        79
        80
        81
        82
        83
        84
        85
        86
        87
        88

    ROWNUM
----------
        89
        90
        91
        92
        93
        94
        95
        96
        97
        98
        99

    ROWNUM
----------
       100
       101
       102
       103
       104
       105
       106
       107
       108
       109
       110

    ROWNUM
----------
       111
       112
       113
       114
       115
       116
       117
       118
       119
       120
       121

    ROWNUM
----------
       122
       123

123 rows selected.

SQL>

So, to restrict the output to 1 row from any table you can write:

SQL> select sysdate
  2  from user_objects
  3  where rownum = 1;

SYSDATE
---------
03-JUN-08

SQL>

and be guaranteed of returning only one row (well, unless the table is completely empty). ROWNUM starts with 1 ONLY when rows are successfully returned based upon the specified criteria. The following query, as shown, returns nothing:

SQL> select object_name
  2  from user_objects
  3  where rownum = 44;

no rows selected

SQL>

Since ROWNUM can never assign the value of 1 it never gets to your desired value of 44 and, unfortunately, never returns any data. There is a ‘workaround’ for this, but it sidetracks the discussion on date strings and I’ll save it for another post.

To be guaranteed of always returning ONE value from a pseudo-column Oracle has provided a wonderful table named DUAL which contains, you guessed it, only one row. Miraculous. So, you can ALWAYS have one row returned without any fuss:

SQL> select sysdate from dual;

SYSDATE
---------
03-JUN-08

SQL>

I’ve digressed a bit from our date discussion to define a couple of (as I see them) useful pseudo-columns, ROWNUM and SYSDATE (and, gee, one of them is date-related). But the thrust here is to provide a way for you to extract useful information from Oracle DATE fields. And I think the examples provided should help you do just that. And, just to be on the safe side, let’s extract the date and time from SYSDATE in several ways:

SQL> select to_char(sysdate, 'MM-DD-YYYY') from dual;

TO_CHAR(SY
----------
06-04-2008

SQL> select to_char(sysdate, 'Month DDth, YYYY') from dual;

TO_CHAR(SYSDATE,'MON
--------------------
June      04TH, 2008

SQL> select to_char(sysdate, 'DD/MM/YYYY') from dual;

TO_CHAR(SY
----------
04/06/2008

SQL> select to_char(sysdate, 'HH24:MI:SS') from dual;

TO_CHAR(
--------
14:50:49

SQL> select to_char(sysdate, 'HH:MI:SS AM') from dual;

TO_CHAR(SYS
-----------
02:51:00 PM

SQL>

Let’s even convert a date string from one format to another:

SQL> select to_char(to_date('1/1/2009','mm/dd/rrrr'), 'dd-mon-rr')
  2  from dual;

TO_CHAR(T
---------
01-jan-09

SQL>

Or convert a date string with non-format characters:

SQL> select to_date('2011-05-24T23:21:30.000Z','yyyy-mm-dd"T"hh24:mi:ss".000Z"') from dual;


TO_DATE('2011-05-24
-------------------
2011-05-24 23:21:30


1 row selected.
SQL>

[My thanks to Wolfgang Breitling for reminding me that non-standard characters can be included in a format mask as long as they are enclosed in quotation marks and for providing the example shown above.]

I suppose you could also update just the time portion of a date field, but it gets rather convoluted:

SQL> select *
  2  from datetst;

MYDATE
--------------------
03-JUN-2008 07:49:12
30-MAY-2008 00:00:00

SQL> update datetst
  2  set mydate = to_date(to_char(mydate, 'MM-DD-YYYY')||' 23:59:59', 'MM-DD-YYYY HH24:MI:SS')
  3  where to_char(mydate, 'DD-MON-YY') = '30-MAY-08';

1 row updated.

SQL> select *
2 from datetst;

MYDATE
--------------------
03-JUN-2008 07:49:12
30-MAY-2008 23:59:59

SQL>

And you can also use Julian date format, which counts the number of days from 01-01-4712 BC to the desired date, putting these values in the 2.4 million range of numbers:

SQL> select to_char(sysdate, 'J') from dual;

TO_CHAR
-------
2454642

SQL>

And, yes, you can go through the gyrations of converting the dates in question to Julian format and subtracting them to get the number of days between, but simple subtraction of standard DATE format values will also provide basically the same result:

SQL> select to_number(to_char(sdate2, 'J')) - to_number(to_char(sdate1, 'J')) diff
2 from dual;

DIFF
-------
      1

SQL>

Straight subtraction of the two dates would produce the days difference as well, but would also include the fractional part of the day:

SQL> select sdate2 - sdate1 diff from dual;

DIFF
----------
.958333333

SQL>

A quick call to ROUND() ‘fixes’ the issue:

SQL> select round(sdate2 - sdate1, 0) diff from dual;

DIFF
-------
      1

SQL>

Yet another way is to truncate the date values, then subtract:

SQL> create table diffdt(
  2          sdate1  date,
  3          sdate2  date
  4  );

Table created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .1, sysdate);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select sdate2 - sdate1
  2  from diffdt;

SDATE2-SDATE1
-------------
   .668564815

SQL>
SQL> select trunc(sdate2) - trunc(sdate1)
  2  from diffdt;

TRUNC(SDATE2)-TRUNC(SDATE1)
---------------------------
                          1

SQL>

We can even get complex and convert a date string in one format to a date string in another format:

SQL> select to_char(to_date('17-dec-80','DD-mon-RR'), 'mm/dd/yyyy') mydate
  2  from dual
  3  /

MYDATE
----------
12/17/1980

SQL> select to_char(to_date('1/1/2009','mm/dd/rrrr'), 'Month, RRRR') mydate
  2  from dual;

MYDATE
---------------
January  , 2009

SQL> select to_char(to_date('1/1/2009','mm/dd/rrrr'), 'Month Dth, RRRR') mydate
  2  from dual;

MYDATE
-------------------
January   5th, 2009

SQL> select to_char(to_date('1/1/2009','mm/dd/rrrr'), 'Month DD, RRRR') mydate
  2  from dual;

MYDATE
------------------
January   01, 2009

SQL>

Using the RR format specifier in place of the YY specifier allows Oracle to return the ‘correct’ four-digit year (presuming the date in this example is a historical one). Using YY in place of RR produces:

SQL> select to_char(to_date('17-dec-80','DD-mon-YY'), 'mm/dd/yyyy') mydate
  2  from dual;

MYDATE
----------
12/17/2080

SQL>

which is, in my mind, clearly not the year the original string represents (as I expect this date comes from the standard EMP play table provided by Oracle). Remember, though, that display formats are just that and have no bearing on how the actual date is stored in Oracle.

Can Oracle handle ‘pre-historic’ dates? Well, it can handle dates as far back as January 1, 4712 BC:

SQL> create table old_dts(
  2          really_old_dt date
  3  );

Table created.

SQL>
SQL> insert into old_dts
  2  values (to_date('-4712/01/01', 'syyyy/mm/dd'));

1 row created.

SQL>
SQL> alter session set nls_date_Format = 'MM/DD/YYYY AD';

Session altered.

SQL>
SQL> select really_old_dt
  2  from old_dts;

REALLY_OLD_DT
-------------
01/01/4712 BC

SQL>

So, why stop at January 1, 4712 BC? Let’s find out:

SQL> select to_char(really_old_dt, 'J') julian
  2  from old_dts;

JULIAN
-------
0000001

SQL>

Seems it’s the starting point for Julian date generation. Since that mechanism is integral to generating dates in Oracle’s internal date format it’s the logical place to start. Which is inconvenient if you have those in your family tree born, say, on August 23, 4728 BC as no corresponding Julian date number exists.

You don’t need to specify four-digit dates to Oracle, either, as one, two and three-digit years are valid:

SQL> select to_date('01SEP1', 'DDMONRRRR') from dual;

TO_DATE('01
-----------
01-SEP-2001

SQL> select to_date('01SEP01', 'DDMONRRRR') from dual;

TO_DATE('01
-----------
01-SEP-2001

SQL> select to_date('01SEP001', 'DDMONRRRR') from dual;

TO_DATE('01
-----------
01-SEP-2001

When you’re using the RRRR format you get the current century by default; using YYYY instead produces different results:

SQL> select to_date('01SEP1', 'DDMONYYYY') from dual;

TO_DATE('01
-----------
01-SEP-0001

SQL>
SQL> select to_date('01SEP01', 'DDMONYYYY') from dual;

TO_DATE('01
-----------
01-SEP-0001

SQL>
SQL> select to_date('01SEP001', 'DDMONYYYY') from dual;

TO_DATE('01
-----------
01-SEP-0001

SQL>

No errors were generated proving that any valid year can be provided to the function and, depending on the format you use, valid dates are returned.

You can also adjust the SYSDATE value forwards, or backwards, by adding or subtracting hours but it requires that you divide the total hours of adjustment by 24 since the expected adjustment interval is in days. To add 72 hours to the current SYSDATE value:

SQL> select sysdate, sysdate +(72/24)
  2  from dual;

SYSDATE   SYSDATE+(
--------- ---------
01-JUL-08 04-JUL-08

SQL>

It’s just as easy to subtract 72 hours:

SQL> select sysdate, sysdate - (72/24)
  2  from dual;

SYSDATE   SYSDATE-(
--------- ---------
01-JUL-08 28-JUN-08

SQL>

You can also adjust the value hy minutes; use 1440 in place of 24 in the above query. And, if you want to adjust by seconds, use 86400 instead of 1440. Pretty slick stuff.

Oh, if you want the number of elapsed seconds you can get that, too, but it’s only reported since midnight of the date supplied:

SQL> select to_number(to_char(sysdate, 'SSSSS')) seconds
  2  from dual;

   SECONDS
----------
     33603

SQL>

So if you try to use that logic to add the total seconds for two dates:

SQL> select to_number(to_char(sysdate, 'SSSSS')) + to_number(to_char(sysdate-32, 'SSSSS')) seconds
  2  from dual;

   SECONDS
----------
     67206

SQL>

you return a horribly incorrect answer because the sum is only calculating the seconds from midnight for each day involved. To get the total seconds for a given date range a different query is required:

SQL> select (sysdate - (sysdate-32))*86400 seconds
  2  from dual;

   SECONDS
----------
   2764800

SQL>

Notice the difference in the values returned; there are obviously far more than 67206 elapsed seconds in a 32-day span. Time flies, but not nearly that quickly.

What if you want the difference displayed in hours, minutes and seconds? That’s an easy task because, since 9.2, Oracle provides the INTERVAL data type. Converting subtracted dates to an INTERVAL is easy, as shown below. The first set of output is from 9.2.0.8:

SQL> create table diffdt(
  2          sdate1  date,
  3          sdate2  date
  4  );

Table created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .1, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .3, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .5, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .8, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - 1, sysdate);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select (sdate2 - sdate1) day(9) to second
  2  from diffdt;

(SDATE2-SDATE1)DAY(9)TOSECOND
---------------------------------------------------------------------------
+000000000 12:55:56
+000000000 17:43:56
+000000000 22:31:56
+000000001 05:43:56
+000000001 10:31:56

SQL>

The next set of results are from an 11.1.0.6 instance:

SQL> create table diffdt(
  2          sdate1  date,
  3          sdate2  date
  4  );

Table created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .1, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .3, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .5, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - .8, sysdate);

1 row created.

SQL>
SQL> insert into diffdt
  2  values (trunc(sysdate) - 1, sysdate);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select (sdate2 - sdate1) day(9) to second
  2  from diffdt;

(SDATE2-SDATE1)DAY(9)TOSECOND
---------------------------------------------------------------------------
+000000000 12:53:20.000000
+000000000 17:41:20.000000
+000000000 22:29:20.000000
+000000001 05:41:20.000000
+000000001 10:29:20.000000

SQL>

Note that in 9.2 the output is in days hours:minutes:seconds, and in 11.1 and later releases the output includes the fraction of a second placeholder (since these are dates, not timestamps, the additional timing detail is not available). If we choose to use the SYSTIMESTAMP pseudocolumn in place of SDATE2 we can return the fractions of a second:

SQL> select (systimestamp - sdate1) day(9) to second
  2  from diffdt;

(SYSTIMESTAMP-SDATE1)DAY(9)TOSECOND
---------------------------------------------------------------------------
+000000000 13:14:42.096000
+000000000 18:02:42.096000
+000000000 22:50:42.096000
+000000001 06:02:42.096000
+000000001 10:50:42.096000

SQL>

How do we do this? Look carefully at the query syntax and you’ll see the DAY(width) TO SECOND directive, where the (width) parameter is optional. This tells Oracle to return the difference as an interval of days, houre, minutes, seconds and, if in a 10g or later database, the fractional part of a second. Any interval less than a day is returned in the +days hours:minutes:seconds.fraction format with the +days value as 0. Any difference greater than a day includes the number of days difference plus the time less than a day in hours, minutes, seconds and fractions thereof. Boy, you can’t get much easier than that.

[You can also set the format for timestamp values to display military time using

SQL > alter session set NLS_TIMESTAMP_TZ_FORMAT='RRRR-MM-DD HH24:MI:SS.FF TZR';

Session altered.

SQL > select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
2010-04-15 08:15:13.287210 -07:00

SQL >

]

So, have yourself a date. Or two. Or three. They’re fun.