ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Mastering Oracle SQL Date Arithmetic

Updated on June 28, 2016

Topics Introduction

Date Arithmetic is the calculations based on date. Doing Date calculations is a night mare many times for a developer, especially in Oracle many developer felt this. But many of us never knew simple tricks and the new features (like INTERVAL introduced in Oracle 9). If you know and understand various date functions thoroughly, you may not feel this as a pain.

For a better understanding, let me put the various possible operations as topics here.

  • Add days, months, years to Date
  • Subtract days, months, years from date
  • Difference between dates
  • Comparing Dates
  • Some Special Oracle date functions
  • Interval and timestamp data types
  • Type Conversion

If you explore these simple steps thoroughly, you may feel your self as an expert of date arithmetic in Oracle. After reading this ready the topic on Oracle Analytic Functions which could make you an expert.

Arithmetic fun

Add days, months, years to Date

Adding to a date is as simple as adding numbers. Yes you can use ‘+' operator straight away. For example SYSDATE+7 gives you 7 days or a week after SYSDATE. So, if you want to add one month you will easily conclude SYSDATE + 30. Good. But this is not correct.

What would be the result of SYSDATE + 30 if the number of days in current month is 31? it would give one day lesser than what we expect. If SYSDATE is 01-SEP-2008, the result will give 01-OCT-2008 and which is seamlessly wrong. This is where Oracles ADD_MONTHS date function plays the role. ADD_MONTHS(SYSDATE, 1) will give the date after a month. The second parameter is number of months and here it says add 1 month to SYSDATE. No need to worry whether it is January or February or August etc.

Ok! I want to take you one step ahead, how to calculate the day after one year. I would give you a clue. And the clue is - 'A year has only 12 months'. Yes you are correct ADD_MONTHS(SYSDATE, 12) will give day after 1 year.

Did you thought ADD_YEARS(SYSDATE, 1)? Ha Ha! Good thinking! But Oracle has not yet provided such function. You can create a custom function using above calculation.


  today DATE :=sysdate;
  future DATE;
  future := today + 45;
  dbms_output.put_line('Today is:' || today);
  dbms_output.put_line('Day after 45 days is:' || future);
  future := ADD_MONTHS(today, 2);
  dbms_output.put_line('Day after 2 months is:' || future);
  future := ADD_MONTHS(today, 12);
  dbms_output.put_line('Day after 1 year is:' || future);
Output will be 
Today is:01-SEP-08
Day after 45 days is:16-OCT-08
Day after 2 months is:01-NOV-08
Day after 1 year is:01-SEP-09

Subtract days, months, years from a date in Oracle

Yes you will be picking up fast if you say just replace negative sign in place of the + operator. But what happens to ADD_MONTHS? Is there is a function SUBTRACT_MONTHS? Ahhhh! But not that. Just put the number of months to add in negative sign.


  today DATE :=sysdate;
  past DATE;
  past := today - 45;
  dbms_output.put_line('Today is:' || today);
  dbms_output.put_line('Day before 45 days is:' || past);
  past := ADD_MONTHS(today, -2);
  dbms_output.put_line('Day before 2 months is:' || past);
  past := ADD_MONTHS(today, -12);
  dbms_output.put_line('Day before 1 year is:' || past);
Output will be
Today is:02-SEP-08
Day before 45 days is:19-JUL-08
Day before 2 months is:02-JUL-08
Day before 1 year is:02-SEP-07

Find Difference between dates in Oracle

Still we can use very simple of operations by using - operator which gives number of days between two dates. Refer below example.

   d1 DATE := '01-Aug-2008';
   today DATE := TRUNC(SYSDATE);
   dbms_output.put_line('No of days between two dates:' || (today-d1) );


Output will be
No of days between two dates:31

Looks very simple right? Errrr! But what is that TRUNC? This function cuts the time part of sysdate. Just place SYSDATE in place of TRUNC(SYSDATE), you will see the same output as fraction. The fraction reflects the time difference. So, Just to ignore that I have put this.

Now, you may think how to interpret into month or year? Here is the role of the Oracle date function MONTHS_BETWEEN comes.

MONTHS_BETWEEN(date1, date2) gives the number of months between two dates. Say I have joined in a company on '01-Jul-2007' and still working in that company. Below code will give my experience in months.

  joined_on DATE := '01-JUL-2007';
  today DATE := TRUNC(sysdate, 'mm');
  dbms_output.put_line('I have bee working here for ' || 
                     MONTHS_BETWEEN(today, joined_on) || ‘ months' );
Output will be
I have bee working here for 14 months

Interesting? I think now you are more confident with date arithmetic than you are earlier.

Comparing Dates

This is not as complex as previous ones. This similar to the fashion you compare other data (numeric/character). Ye you can use the operators <, >, =, <> operators.

Below example illustrates simply to you straight away. The example analyzes between three brothers John, Peter and David taking their date of birth.

   johns_dob DATE := '01-JUL-1960';
   peters_dob DATE := '01-JUL-1970';
   davids_dob DATE := '01-JUL-1960';
   IF johns_dob <> peters_dob THEN
   --both born on different dates
      IF johns_dob < peters_dob THEN
         dbms_output.put_line('John is younger to Peter!');
         dbms_output.put_line('John is elder to Peter!');
      END IF;
   elsif johns_dob = peters_dob THEN
      dbms_output.put_line('John and Peter are Twins!');
      dbms_output.put_line('This is impossible any way!! ');
      --Find out why this is never possible if you give values!
      --A logical test for you! Solve & Give your comments --
   END IF;
   IF johns_dob <> davids_dob THEN
      --both born on different dates
      IF johns_dob < davids_dob THEN
         dbms_output.put_line('John is younger to David!');
         dbms_output.put_line('John is elder to David!');
      END IF;
   elsif johns_dob = davids_dob THEN
      dbms_output.put_line('John and david are Twins!');
   END IF;
Output should be:
John is younger to Peter!
John and david are Twins!

I would expect your comments if you have read above example thoroughly.

Time Machine

Some special date functions

Yes there are more features in Oracle to perform date arithmetic. I list them here for your reference.

SYSDATE returns the current date and time of your operating system (the place where your oracle server is running). This is a pseudo column.

SYSTIMESTAMP similar to SYSDATE but it includes fractional seconds and time zone.

TRUNC(date) trims off the time portion of given date.

TRUNC(date, ‘mm') returns the first day of month for given date.

LAST_DAY(date) returns the last day of month for given date.

EXTRACT extracts and returns the specified value from a date or time. You can pull the day, month, year, hour and more from a date using below syntax.

FROM { date_value | interval_value } )

But important thing is you can pull only a day, month or year from a date value. For pulling hours, minutes, time zone you need to use interval value using which you can't pull day/month/year.



Type Conversion of Oracle Date Data Type

Oracle provides many functions for date and time conversions. Apart from that, TO_DATE and TO_CHAR are very familiar functions.

  • TO_DATE converts character data into a DATE.
  • TO_CHAR converts DATE into character data.


  • TO_DATE(‘25/01/2008', ‘dd/mm/yyyy') - Returns the value '25-JAN-08' of type date.
  • TO_CHAR(SYSDATE, ‘MON') - Returns the abbreviated month name

There are so many other functions available for conversion of intervals. I have listed them below.

  • NUMTODSINTERVAL converts a NUMBER or expression into a DAY TO SECOND interval.
  • NUMTOYMINTERVAL, converts a NUMBER or expression into a YEAR TO MONTH interval.
  • TO_DSINTERVAL converts string to DAY TO SECOND interval.
  • TO_YMINTERVAL converts a character string to YEAR TO MONTH type
  • TO_TIMESTAMP_TZ converts character data to TIMESTAMP WITH TIME ZONE

I leave the examples for conversion as an exercise for you with the hope that you have got enough confidence now.

Imporve your aritmetic skills!

See Also - Other Articles on Oracle


    0 of 8192 characters used
    Post Comment
    • selvirajan profile imageAUTHOR


      4 years ago from India

      Back here after few years. Glad that still this helped many people.

      Thanks to all the viewers who made this interactive while i am away. Would try to catch up slowly and regularly.

    • profile image


      4 years ago

      Thanks for introducing a little ratioanltiy into this debate.

    • Cyrille MODIANO profile image


      6 years ago from Montreal

      very well explained, thank you for sharing this !

    • profile image


      8 years ago

      nicely explained the simple and important topic; I used to face a lot many problems with date calculations including date format specification of different countries; ur hub is informative. thanks

    • profile image


      9 years ago

      can u tell the answer to comparing dates question

    • profile image


      9 years ago

      ITs toooo gud and very helpful.....

    • profile image


      10 years ago

      I'm sorry, the actual code is,


      SET @today = getdate()


      SET @BEFORE = getdate()-30

      SELECT COUNT(InterActionID) as total


      WHERE CAST(CONVERT(DATETIME, '1970-01-01 00:00:00.000') + dateadd(hh,-7,(STARTDATE/86400.0)) AS DATETIME)

      between @before AND @today

      and Originator = '1234567891'

    • profile image


      10 years ago

      Would some one please help me translate this to Oracle SQL ?




      SET @BEFORE = GETDATE()-30



      WHERE CAST(CONVERT(DATETIME, '1970-01-01 00:00:00.000') + DATEADD(HH,-7,(STARTDATE/86400.0))



    • gramsmith profile image


      10 years ago

      Thanks for this great article.Keep on writing.............

    • profile image


      10 years ago

      oracle arithmetic does not work as you mentioned "What would be the result of SYSDATE + 30 if the number of days in current month is 31? it would give one day lesser than what we expect. If SYSDATE is 01-SEP-2008, the result will give 31-SEP-2008" , you have got it wrong

    • profile image

      Niels Jessen 

      10 years ago


      Calculate months between dates; months_between(date1,date2) ==> example ==>

      months_between(trunc(sysdate + 394,'mm'),trunc(sysdate,'mm'))

      Calculate years between dates: months_between(date1,date2)/12 ==> example

      select months_between(trunc(sysdate + 394,'mm'),trunc(sysdate,'mm'))/12 from dual;

    • profile image


      10 years ago

      How to calculate no. of days,months and years between two dates. Dates Like that first date is '01-Jan-2007' and second date is 15-july-2009.

    • profile image

      New Dating Sites 

      11 years ago

      Great page lol @ Bharath

    • profile image


      11 years ago

      I learnt many new things from this website. gr8 site.

    • profile image


      11 years ago

      Too Good

    • profile image


      11 years ago from United States

      Nice hub!

    • profile image


      11 years ago from United States

      Nice hub!

    • profile image


      11 years ago

      Awesome....So helpful....Thanks a lot.

    • selvirajan profile imageAUTHOR


      11 years ago from India

      Thank you hot dorkage for your comments.

      I liked your hubs on oracle. Thats really interesting. Especially the series of articles on &quot;SQL Sins&quot;. Its really a honour to get appreciating comments from a expert here.

    • hot dorkage profile image

      hot dorkage 

      11 years ago from Oregon, USA

      Great hub it's nice to compare the difference between Oracle SQL and mySQL. I wonder how many hubbers are into raw Oracle SQL though.


    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at:

    Show Details
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the or domains, for performance and efficiency reasons. (Privacy Policy)
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
    ClickscoThis is a data management platform studying reader behavior (Privacy Policy)