Performing Math on Oracle Dates

Here are some quick and dirty examples for adding and subtracting days and months and finding the difference between dates in Oracle.

These examples select the result from the ‘dual’ table. Dual is a dummy table that exists in all Oracle databases. I am also using sysdate heavily. Sysdate simply returns the current date and time.

Adjusting Days, Weeks, Hours and Minutes

To add and subtract days from a date we simply use + or -. Here are some examples:

SQL> SELECT sysdate + 7 FROM dual;

SYSDATE+7
---------
06-DEC-05

SQL> SELECT sysdate - 30 FROM dual;

SYSDATE-3
---------
30-OCT-05

SQL> SELECT to_char(sysdate - 14, 'MM/DD/YYYY HH:MI AM') FROM dual;

TO_CHAR(SYSDATE-14,
-------------------
11/15/2005 09:41 AM

In the first example we see that the query returns 7 days from today. The second one shows 30 days before today. In the third query I have added the to_char function so we see hour and minute. This shows us that while the date has changed, the time has not.

The first and third examples above also show how adding days would be used to add weeks. If you wanted to simplify this you could add the logic into the query like this:

SQL> SELECT sysdate + (7 * 2) FROM dual;

SYSDATE+(
---------
13-DEC-05

Likewise to work with hours you would use fractional days. The simplest way to show this is by dividing the hours by 24 right in the query. The parentheses may not be necessary, but I feel they make the query a little clearer.

Here I show the current time, then the current time plus two hours:

SQL> SELECT to_char(sysdate, 'HH:MI AM') FROM dual;

TO_CHAR(
--------
09:48 AM

SQL> SELECT to_char(sysdate + (2/24), 'HH:MI AM') FROM dual;

TO_CHAR(
--------
11:48 AM

An alternative to this method is to use the numtodsinterval function. The example above can instead be written like this:

SQL> SELECT
to_char(sysdate + numtodsinterval(2, 'HOUR'), 'HH:MI AM')
FROM dual;

TO_CHAR(
--------
11:57 AM

Here the numtodsinterval function is doing the work of dividing 2/24 for hours. Valid options for the numtodsinterval are ‘DAY’, ‘HOUR’, ‘MINUTE’, or ‘SECOND’. Here is an example using ‘MINUTE’. When working with minutes the numtodsinterval function is much more readable.

SQL> SELECT
to_char(sysdate + numtodsinterval(45, 'MINUTE'), 'HH:MI AM')
FROM dual;

TO_CHAR(
--------
10:47 AM

Adjusting Months and Years

To work with months and years (either of which may have a varying number of days) Oracle has provided the function numtoyminterval. This works much like the numtodsinterval function mentioned above by taking a number and a string. Valid options for the string are ‘YEAR’ or ‘MONTH’.

SQL> SELECT
to_char(sysdate + numtoyminterval(5, 'MONTH'), 'MM/DD/YYYY')
FROM dual;

TO_CHAR(SY
----------
04/29/2006

SQL> SELECT
to_char(sysdate + numtoyminterval(2, 'YEAR'), 'MM/DD/YYYY')
FROM dual;

TO_CHAR(SY
----------
11/29/2007

Comparing dates

Let’s say we want to compare some dates and find out how many days or weeks between them. To simply see the result in days we can use the minus operator like this:

SQL> SELECT
TO_DATE('12/25/2005', 'MM/DD/YYYY') - sysdate
FROM dual;

TO_DATE('12/25/2005','MM/DD/YYYY')-SYSDATE
------------------------------------------
26.5124421

The result is expressed in days including fractional hours. Of course if we wanted weeks we could just divide by 7. Similarly if we’re looking for hours we could multiply by 24, but if we want months we need to use the months_between function.

SQL> SELECT
months_between(sysdate, to_date('01/01/2006', 'MM/DD/YYYY'))
FROM dual;

MONTHS_BETWEEN(SYSDATE,TO_DATE('01/01/2006','MM/DD/YYYY'))
----------------------------------------------------------
-1.0829409

Finding the Latest or Earliest Date in a Set

The Oracle functions greatest and least can be used on dates to return the latest or earliest date.

SQL> SELECT
greatest(sysdate, to_date('09/11/2005','MM/DD/YYYY'),
to_date('12/25/2005','MM/DD/YYYY'))
FROM dual;

GREATEST(
---------
25-DEC-05

SQL> SELECT
least(sysdate, to_date('09/11/2005','MM/DD/YYYY'),
to_date('12/25/2005','MM/DD/YYYY'))
FROM dual;

LEAST(SYS
---------
11-SEP-05

What’s the Last Day in a Given Month

The last_day function can be used to return the last day in a given month.

SQL> select last_day(sysdate) from dual;

LAST_DAY(
---------
30-NOV-05

Next Day of the Week After a Date

The next_day function gives the date of the next occurrence of a day of the week (‘Monday’, ‘Tuesday’, etc.) after a given date. Here we see the date of the next Sunday after today:

SQL> select next_day(sysdate,'Sunday') from dual;

NEXT_DAY(
---------
04-DEC-05

Still haven’t found what you’re looking for?

You may also want to look at these other articles:

Converting Time Zones in Oracle
Oracle, SQL, Dates and Timestamps
UNIX timestamp to Oracle Date Conversion

oracle, sql, dba, database administration, database development

26 thoughts on “Performing Math on Oracle Dates”

  1. Hi,

    I want to find day function or derive method for the last workday/weekday of a month. For ex: 12/30/2005 was the last weekday of December.

    Your help is much appreciated.

    Thank you

    Sidney

  2. Pingback: Life After Coffee » SQL - Finding the Last Weekday in a Month

  3. here is a simple sql to get last weekday

    With date_v as
    (select last_day(to_date(‘&MM-&DD-&yyyyy’,’MM-DD-YYYY’)) mydate from dual)
    select max(date_v.mydate-(i-1)) last_weekday
    from date_v,(select level i from dual connect by level

  4. sorry the script somehow got cut off

    here it is..

    With date_v as
    (select last_day(to_date(‘&MM-&DD-&yyyyy’,’MM-DD-YYYY’)) mydate from dual)
    select max(date_v.mydate-(i-1)) last_weekday
    from date_v,(select level i from dual connect by levell
    1 With date_v as
    2 (select last_day(to_date(‘&MM-&DD-&yyyyy’,’MM-DD-YYYY’)) mydate from dual)
    3 select max(date_v.mydate-(i-1)) last_weekday
    4 from date_v,(select level i from dual connect by level/
    Enter value for mm: 12
    Enter value for dd: 02
    Enter value for yyyyy: 2005
    old 2: (select last_day(to_date(‘&MM-&DD-&yyyyy’,’MM-DD-YYYY’)) mydate from dual)
    new 2: (select last_day(to_date(’12-02-2005′,’MM-DD-YYYY’)) mydate from dual)

    LAST_WEEK
    ———
    30-DEC-05

    SQL>
    where to_char(date_v.mydate-(i-1),’Dy’) not in (‘Sat’,’Sun’)

  5. hmm..the site does some weird formatting ..
    anyway trying my final attempt

    With date_v as
    (select last_day(to_date(‘&MM-&DD-&yyyyy’,’MM-DD-YYYY’)) mydate from dual)
    select max(date_v.mydate-(i-1)) last_weekday
    from date_v,(select level i from dual connect by level

  6. With date_v as
    (select last_day(to_date(‘&MM-&DD-&yyyyy’,’MM-DD-YYYY’)) mydate from dual)
    select max(date_v.mydate-(i-1)) last_weekday
    from date_v,(select level i from dual connect by level between 1 and 7)
    where to_char(date_v.mydate-(i-1),’Dy’) not in (‘Sat’,’Sun’)

    note to siteowner, your site interprets lessthan sign in a different way and hence my prev posting were cutting off

    level “less than or equal to 7”
    has been rewritten as
    level between 1 and 7

    hopefully this goes through
    🙂

  8. Pingback: Life After Coffee » Getting a specific time of day in Oracle

  10. Warren,

    To find the difference between two dates you can simply subtract date1 from date2.

    The result will be in days, so if you want the result in hours simply multiply by 24, in minutes multiply by 1440.

  11. I want to extract the day of the week from a date value..how do I do it..I need to write a trigger that extracts day of the week from the date

  12. Pink,

    There are a few ways to extract the day of the week from a date depending on what format you want it in. I wonder why you are building a trigger to extract the day of the week, but here are a couple common ways:

    Get the day of the week spelled out:

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

    MONDAY

    Day of the week (not padded with spaces)

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

    MONDAY

    Day number of the week (Sunday is 1)

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

    2

    Three letter abbreviation:

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

    MON

    Hope this helps!

  14. Syam,

    I’m not sure exactly what you’re looking for here. Several date functions can return different numbers. Typically math on dates (like sysdate-my_date) will return a number in days.

    If you can be more specific I’ll try to help you more.

  15. Please note … the ticks & quotes aren’t consistent. Some are styled, backtick’d, etc.

    You’ll want to double check your work if using samples from this page or others.

    Later, Markus.

  17. Just use the above query in post # 6 and change

    With date_v as
    (select last_day(to_date(’&MM-&DD-&yyyyy’,’MM-DD-YYYY’)) mydate from dual)
    select max(date_v.mydate-(i-1)) last_weekday
    from date_v,(select level i from dual connect by level between 1 and 7)
    where to_char(date_v.mydate-(i-1),’Dy’) in (’Sat’,’Sun’))

  18. SELECT
    ( CASE
    WHEN TO_CHAR(l_date, ‘D’) = 1 THEN l_date – 2
    WHEN TO_CHAR(l_date, ‘D’) = 7 THEN l_date – 1
    WHEN TO_CHAR(l_date, ‘D’) BETWEEN 2 and 6 THEN l_date END ) Last_Weekday
    FROM ( SELECT LAST_DAY(l_date) l_date FROM ( SELECT l_date FROM ( SELECT TO_DATE(‘&YYYYMMDD’, ‘YYYYMMDD’) l_date FROM dual ) ) )
    /

  19. i want to select the date just near by to the system date from a given data base .

    Regards:
    paschima

  20. I needed a “where” clause that selected events that occurred in previous week (Sunday midnight to Saturday 23:59:59):


    where
    event_date between
    sysdate - (to_number(to_char(sysdate,'D')) + 6) - ((to_number(to_char(sysdate,'SSSSS'))) / 86400)
    and
    sysdate - to_number(to_char(sysdate,'D')) - (((to_number(to_char(sysdate,'SSSSS'))) / 86400) - .99999)

  21. Hi

    I am looking oracle function where I have to compare two dates and result of comparision expressed in Month and Days.
    Ex
    months_between( To_Date(’16-05-2008′, ‘DD-MM-YYYY’ ), To_Date(’01-04-2008′, ‘DD-MM-YYYY’))

    expected Result 1 month 13 days

    Thanks in Advance

  22. i hav two dates start_date and end_date the dates should be compared with sys date and the previous month of sys date..considering leap year..plz any one can send me the quey for this…

  23. Anyone Please help me in deriving the following

    If i provide a date How can i derive the following using SQL scripts / SQL Queries.

    1. How to get No of Weeks in a particular month.
    2. How to get the Starting date’s of every week
    (Irrespective of Monday/thursday …so on) for that month.
    3. How to get the No of Business Days for every week.

    Find the following example for reference/visualization purposes.

    Ex: 5 Weeks (for the Month of June 2010)

    No of Business
    Days in a Week Date
    ————— ———–
    Week 1 = 4 6/1/2010 (Tue)
    Week 2 = 5 6/7/2010 (Mon)
    Week 3 = 5 6/14/2010 (Mon)
    Week 4 = 5 6/21/2010 (Mon)
    Week 5 = 3 6/28/2010 (Mon)

    We need the above format if we provide the date(06/01/2010 {01-Jun-2010} as a input.

  24. Hello,

    thanks for the tips. but i want to get the number of years between sysdate and a given date. finding it difficult. the date is to be obtained from personal social number in the form yymmdd-xxxx

    this query below is to get the days between then i will have to convert to years.

    select upper(first_name) first_name,upper(last_name) last_name,
    round((to_date(substr(pnr,1,6), ‘yymmdd’)- sysdate), 1)Age from car_owner;

    I am having a problem with my results: e.g. 490321-7899, is giving me 13859.4 days but when i calculate manually it gives me about 22,665 days.

    Need help to go about this.

  25. I am using following query to get difference between dates without considering time.

    select to_date(to_char(sysdate,’dd/mm/yy’),’dd/mm/yy’)- to_date(to_char(status_date,’dd/mm/yy’),’dd/mm/yy’), from status_table;

    so that 11-Aug-2011 – 08-Aug-2011 is always 3.

    Is there any better way to do that?

Leave a Reply

Your email address will not be published. Required fields are marked *