OraFusion.com - Dates in Oracle

Oracle Articles

An introduction to dates in Oracle

(Revised: 03 August 2006)

Introduction:

This article is a brief introduction to dates in Oracle, with a focus on date manipulation using SQL. The topics I discuss include the following:

Date display formats.
Date arithmetic and date functions.

Note only dates are discussed; the newer timestamp and interval data types are not covered. Further, US readers should note that I use a British date format as the default in my examples - i.e. day/month/year.

To start with I'll create a date and store it in a table. I'll use the universal Oracle front-end (i.e. SQL Plus) for all my examples below. Here goes:

SQL> create table date_test as select sysdate dt from dual;

Table created.

SQL> select dt from date_test;

DT
-------------------
21/07/2006 13:58:31

SQL>

In the above, I created a date using the sysdate function, which returns the current system date. When I executed the create table statement, the system date happened to be 31 seconds and 58 minutes past 1:00 pm on 21 July 2006. The subsequent query then displays the resulting stored date, in the format: dd/mm/yyyy hour(in 24 hr format):minute:second. I discuss date display formats later in this article. However, to change your session date display format to the one used above, execute the SQL shown here .

Before proceeding any further, it is worth spending a few lines on how a date is internally represented in Oracle. Oracle stores dates in a 7 byte format as is evident from the following:

SQL> select dump(dt,10) from date_test;

DUMP(DT,10)
------------------------------------------------------------------

Typ=12 Len=7: 120,106,7,21,14,59,32

SQL>

Here I queried the contents of the stored date using the dump function, instructing it to return the internal representation in decimal format (dump returns the internal representation of a stored variable). The result returned the Oracle datatype code (12 for date), its length (7 bytes) and the contents of each of the 7 bytes in decimal format. The internal representation of the date should be read from left to right, as follows: century , year in the century, month, day, hour , minute and second. The century and year are offset by 100 and the hour, minute and second are offset by 1. With that said about internal representation of dates, I'll move on to date formats.

Date formatting:

Standard date display formats vary from country - for example, 21 July 2006 would be written as 07/21/2006 and 21/07/2006 in the US and Australia respectively. This brings up a question: what determines how Oracle displays a date? The "one parameter" answer to the question is: NLS_DATE_FORMAT. This parameter, which governs how dates are displayed by default, is set at the database level during database creation. The default format can be overriden at the session level - I'll demonstrate how a little later. For the moment, though, let's start with displaying dates as strings with specified formats. This is done using the to_char function, which converts a date to a string according to the format specified in the second argument of the function. Below are several examples:

--default format for my session

SQL> select to_char(dt,'dd/mm/yy hh24:mi:ss') from date_test;

TO_CHAR(DT,'DD/MM
-----------------
21/07/06 13:58:31

--two digit day, short month name, 4 digit year, no time

SQL> select to_char(dt,'dd Mon YYYY') from date_test;

TO_CHAR(DT,
-----------
21 Jul 2006

--short day name, two digit day, short month name, 2 digit year, no time

SQL> select to_char(dt, 'Dy dd Mon YY') from date_test;

TO_CHAR(DT,'D
-------------
Fri 21 Jul 06

--full day name, two digit day, full month name, 4 digit year, no time

SQL> select to_char(dt,'Day dd Month YYYY hh24:mi') from date_test;

TO_CHAR(DT,'DAYDDMONTHYYYYHH24:MI'
----------------------------------
Friday 21 July 2006 13:58

--US format date, time in 12 hour mode with AM/PM prefix

SQL> select to_char(dt,'mm/dd/yyyy hh12:mi:ss AM') from date_test;

TO_CHAR(DT,'MM/DD/YYYYH
-----------------------
07/21/2006 01:58:31 PM

In the above, the contents of dt are converted to character strings whose format is specified by the second argument of to_char. I'll say no more about the formats used above, since their meaning should be fairly obvious from the query results. Note that these formats just scratch the surface of the available date format models. Check the Oracle docs for many more, along with a complete discussion of date and timestamp formats. However, there is one further point I'd like to make here. Format models also enable you to extract other "not so commonly used" information relating to a date. Some examples - day of the week, day of the year, week of the year and century (the latter being indispensable if you are Rip van Winkle):

--day of the week

SQL> select to_char(dt,'d') from date_test;

T
-
5

SQL> select to_char(dt,'Day') from date_test;

TO_CHAR(D
---------
Friday

--day of the year

SQL> select to_char(dt,'ddd') from date_test;

TO_
---
202

--week of the year

SQL> select to_char(dt,'ww') from date_test;

TO
--
29

--century!

SQL> select to_char(dt,'CC') from date_test;

TO
--
21

One last point before we end this section: I'd mentioned that the default date display format can be changed at the session level. Here's how:

SQL> alter session set NLS_DATE_FORMAT='dd Mon yyyy hh12:mi:ss AM';

Session altered.

SQL> select dt from date_test;

DT
-----------------------
21 Jul 2006 01:58:31 PM

--change back to original format

SQL> alter session set NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select dt from date_test;

DT
-------------------
21/07/2006 13:58:31

Here I've changed the format from 'dd/mm/yyyy hh24:mi:ss' (the default for my session) to 'dd Mon yyyy hh12:mi:ss AM' and then reset it to the original format. Note, too, that the above queries do not explicitly convert the dates to strings - this is implicitly done "behind the scenes" by Oracle, according to the format specified by NLS_DATE_FORMAT.

This brings me to the end of the discussion on date format models. Be sure to check the Oracle documentation for much more (Note: you will need to register to access the online documentation).

Date arithmetic and date functions :

Adding / subtracting days to / from dates is as simple as adding numbers. Witness:

--subtract 10 days

SQL> select dt - 10 from date_test;

DT-10
-------------------
11/07/2006 13:58:31

This also works for hours, minutes and seconds:

--subtract 3 hrs from date

SQL> select dt-3/24 from date_test;

DT-3/24
-------------------
21/07/2006 10:58:31

--subtract 12 mins from date

SQL> select dt-12/1440 from date_test;

DT-12/1440
-------------------
21/07/2006 13:46:31

--subtract 31 secs from date

SQL> select dt-31/86400 from date_test;

DT-31/86400
-------------------
21/07/2006 13:58:00

One can also subtract two dates (but not add them!):

SQL> select dt-to_date('01-01-06', 'dd-mm-yy') from date_test;

DT-TO_DATE('01-01-06','DD-MM-YY')
---------------------------------
201.582303

The fractional portion returned is 50311/86400 - i.e elapsed seconds since midnight divided by the number of seconds per day.

Oracle provides a range of date functions. Their utility is best seen through examples. So here we go, starting with trunc and round:

SQL> select trunc(dt) from date_test;

TRUNC(DT)
-------------------
21/07/2006 00:00:00

SQL> select round(dt) from date_test;

ROUND(DT)
-------------------
22/07/2006 00:00:00

trunc truncates the time portion of the date to the previous midnight and round rounds down/up to the preceding/following midnight depending on whether time portion of the date is AM/PM. No surprises here. However, there is interesting additional functionality in both functions: they both take an optional second argument; a format which specifies a (non default) level of truncation or rounding. Below are some examples. Beware though, the format model for trunc and round works differently from the date format models discussed earlier. Here are some examples:

--truncate to first day of the month

SQL> select trunc(dt,'mm') from date_test;

TRUNC(DT,'MM')
-------------------
01/07/2006 00:00:00

--truncate to first day of the year

SQL> select trunc(dt,'yy') from date_test;

TRUNC(DT,'YY')
-------------------
01/01/2006 00:00:00

--truncate to first day of the week - NOTE FORMAT!!

SQL> select trunc(dt,'Day') from date_test;

TRUNC(DT,'DAY')
-------------------
17/07/2006 00:00:00

--round to start of nearest month

SQL> select round(dt,'mm') from date_test;

ROUND(DT,'MM')
-------------------
01/08/2006 00:00:00

--round to start of nearest year

SQL> select round(dt,'yy') from date_test;

ROUND(DT,'YY')
-------------------
01/01/2007 00:00:00

--round to start of nearest week. NOTE FORMAT!!

SQL> select round(dt,'Day') from date_test;

ROUND(DT,'DAY')
-------------------
24/07/2006 00:00:00

In all cases the time portion is truncated to midnight of the relevant day. Note, in particular, the examples that use the 'Day' format mask - compare the meaning of 'Day' in trunc and round to its meaning in my earlier discussion of date formats .

Given a date, Oracle provides functions to get one-step answers to the following (function name in brackets):

The last day of the month (last_day)
Add/subtract a certain number of months (add_months)
The date of a specific next day of the week (next_day). For example: what's the date next Tuesday?

Here are examples illustrating each of these:

--last day of the month

SQL> select last_day(dt) from date_test;

LAST_DAY(DT)
-------------------
31/07/2006 13:58:31

--subtract 3 months

SQL> select add_months(dt,-3) from date_test;

ADD_MONTHS(DT,-3)
-------------------
21/04/2006 13:58:31

--what's the date next Tuesday?

SQL> select next_day(dt,'Tuesday') from date_test;

NEXT_DAY(DT,'TUESDA
-------------------
25/07/2006 13:58:31

Quick quiz to check that you're paying attention: why isn't there a first_day function?

Given two dates, months_between returns the number of months between the two dates, with the fractional portion based on a 31 day month. An example:

SQL> select months_between(dt,dt+100) from date_test;

MONTHS_BETWEEN(DT,DT+100)
-------------------------
-3.2580645

As is evident, this returns a negative number if the first argument is less than the second and a positive one vice-versa.

Finally, it is appropriate to close this discussion with the answer to one of the most common questions from developers new to Oracle: how does one convert a string to a date? This is done using the to_date function together with Oracle's versatile date format model. Below I show a few examples:

SQL> select to_date('20062107','yyyyddmm') from dual;

TO_DATE('20062107',
-------------------
21/07/2006 00:00:00

SQL> select to_date('21 July 2006','dd Month YYYY') from dual;

TO_DATE('21JULY2006
-------------------
21/07/2006 00:00:00

SQL> select to_date('07/21/06','mm/dd/yy') from dual;

TO_DATE('07/21/06',
-------------------
21/07/2006 00:00:00

SQL> select to_date('21 Jul 06 1:58:31 PM','dd Mon yy hh12:mi:ss AM') from dual;

TO_DATE('21JUL061:5
-------------------
21/07/2006 13:58:31

--to_char is the inverse of to_date

SQL> select to_date(to_char(dt,'dd/mm/yy hh24:mi:ss'),'dd/mm/yy hh24:mi:ss') from date_test;

TO_DATE(TO_CHAR(DT,
-------------------
21/07/2006 13:58:31

The last statement simply shows that to_date is the inverse of to_char - i.e the application of to_date to the character string returned by to_char returns the original date provided the same format used in both functions.

In closing

This brings me to the end of my tour of dates in Oracle. The content is based on "how to" questions that I've received from colleagues over the last few years. I've tried to keep it short - but I hope it is comprehensive enough to answer date-related questions that developers new to Oracle might have.

Back to the top