Oracle Tips and Tricks — David Fitzjarrell

June 3, 2008

How About A Date?

Filed under: dates,General — dfitzjarrell @ 12:28

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(, [format])

where 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.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: