(Try saying that 5 times quickly in a row) 🙂
There’s an old saying that’s gone around for years in IT circles which is “If you have a text parsing problem you can use a regular expression…now you have two problems“.
I’d like to steal that phrase and manipulate it for the sake of today’s post, which is, “If someone gives you a couple of dates, then you have no problems. But if you want to do arithmetic on those two dates, then you have a whole world of problems” 🙂
At first glance, when you just get rolling with dates in the Oracle database, doing date arithmetic seems pretty easy. If I have a couple of dates, in this case obviously 1000 days apart, then getting the days difference is simple subtraction.
SQL> select sysdate date1, sysdate-1000 date2 ;DATE1 DATE2--------- ---------17-AUG-26 21-NOV-231 row selected.SQL>SQL> with t as ( select sysdate date1, sysdate-1000 date2 ) 2 select date2 - date1 days 3 from t; DAYS---------- -1000
It’s when you diverge from days and start looking at things like weeks and months and years that things start to get a little bit more complicated. For a long time there have been some Oracle provided functions to help in this area. For example to get the months between two dates I can use the logically named MONTHS_BETWEEN function. Of course, given the inconsistent number of days in a month spanning across all the months or even spanning within the same month in a leap year, then a decimal portion of a month seems an odd choice.
(Spoiler alert: The decimal portion assumes 31days always)
SQL> with t as ( select sysdate date1, sysdate-1000 date2 ) 2 select months_between(date1,date2) months 3 from t; MONTHS----------32.8709677
But once we get to years, then things get a bit more tricky. Our first attempt might be to simply divide the number of days by 365 and just hope that things like leap years and niche boundaries don’t cause you any problems.
SQL> with t as ( select sysdate date1, sysdate-1000 date2 ) 2 select (date2-date1)/365 years_maybe 3 from t;YEARS_MAYBE----------- -2.739726
Alternatively, I could use the previously mentioned MONTHS_BETWEEN function and do a division by 12 to get perhaps a slightly more robust definition of the number of years between two dates.
SQL> with t as ( select sysdate date1, sysdate-1000 date2 ) 2 select trunc(months_between(date1,date2))/12 years_maybe 3 from t;YEARS_MAYBE----------- 2.66666667
Compounding all of this is when we get into TIMESTAMP data type territory, where the rules are different. Subtracting to TIMESTAMP values does not give you a numeric result like the subtraction of two dates. It gives you an interval data type result, in this case a DAY TO SECOND interval.
SQL> with t as ( select localtimestamp date1, localtimestamp-numtodsinterval(1000,'DAY') date2 ) 2 select date2 - date1 days 3 from t;DAYS--------------------------------------------------------000001000 00:00:00.000000000
If you want that interval converted to be a numeric value, which is what most people would expect when you’re talking about the number of days between two dates, then you now have to use the EXTRACT function.
SQL> with t as ( select localtimestamp date1, localtimestamp-numtodsinterval(1000,'DAY') date2 ) 2 select extract(DAY from date2 - date1) days 3 from t; DAYS---------- -1000
But even that gets a bit more complicated because if I want to get the number of years between two TIMESTAMPs, then the intuitive thing of simply replacing the DAY clause with the YEAR clause gives me an error.
SQL> with t as ( select localtimestamp date1, localtimestamp-numtodsinterval(1000,'DAY') date2 ) 2 select extract(YEAR from date2 - date1) days 3 from t;select extract(YEAR from date2 - date1) days *ERROR at line 2:ORA-30076: invalid extract field for extract source
You can fix this with an appropriate data type conversion, but let’s face it – it all just gets a bit messy.
Thankfully in 26ai, a lot of that nuance and complexity disappears with our DATEDIFF function. You simply nominate the time unit you’re interested in, whether it’s year, month, etc, and then pass in your two dates. Whether it’s a timestamp or whether it’s a date, it doesn’t matter. I can get months, seconds, microseconds, even nanoseconds on platforms that support that level of detail. And the result always comes as a number. Moreover, that number is an integer not some strange decimal component of a month or a day.
SQL> select DATEDIFF(year,sysdate-1000,sysdate) yr; YR---------- 31 row selected.SQL>SQL> select DATEDIFF(month,sysdate-1000,sysdate) mth; MTH---------- 331 row selected.SQL>SQL> select DATEDIFF(second,sysdate-1000,sysdate) secs; SECS---------- 864000001 row selected.SQL>SQL> select DATEDIFF(microsecond,systimestamp-numtodsinterval(3.5,'SECOND'),systimestamp) us; US---------- 3500000
So whilst that makes things so much easier, it also serves as a cautionary note in that you should not simply just race out and look for all your examples of MONTHS_BETWEEN and EXTRACT functions and just replace them with a DATEDIFF function. You should definitely be testing because you will get different results.
This is especially important when you start looking at the difference between DATEDIFF on month boundaries and the MONTHS_BETWEEN function. The key thing with DATEDIFF is the calculation is defined by how many times you cross a month boundary. The number of days between two dates does not play a role. It is solely about how often you cross the start of a month. We can see that from some of the examples below.
SQL> select 2 datediff(month, date '2024-01-31', date '2024-02-01') as datediff_month, 3 months_between(date '2024-02-01', date '2024-01-31') as months_between 4 from dual;DATEDIFF_MONTH MONTHS_BETWEEN-------------- -------------- 1 .0322580651 row selected.SQL>SQL> select 2 datediff(month, date '2024-01-01', date '2024-01-31') as datediff_month, 3 months_between(date '2024-01-31', date '2024-01-01') as months_between 4 from dual;DATEDIFF_MONTH MONTHS_BETWEEN-------------- -------------- 0 .9677419351 row selected.SQL>SQL> select 2 datediff(month, date '2024-02-28', date '2024-03-01') as datediff_month, 3 months_between(date '2024-03-01', date '2024-02-28') as months_between 4 from dual;DATEDIFF_MONTH MONTHS_BETWEEN-------------- -------------- 1 .1290322581 row selected.SQL>SQL> select 2 datediff(month, date '2024-02-29', date '2024-03-01') as datediff_month, 3 months_between(date '2024-03-01', date '2024-02-29') as months_between 4 from dual;DATEDIFF_MONTH MONTHS_BETWEEN-------------- -------------- 1 .0967741941 row selected.SQL>SQL> select 2 datediff(month, date '2024-01-15', date '2024-02-14') as datediff_month, 3 months_between(date '2024-02-14', date '2024-01-15') as months_between 4 from dual;DATEDIFF_MONTH MONTHS_BETWEEN-------------- -------------- 1 .9677419351 row selected.SQL>SQL> select 2 datediff(month, date '2024-01-15', date '2024-01-16') as datediff_month, 3 months_between(date '2024-01-16', date '2024-01-15') as months_between 4 from dual;DATEDIFF_MONTH MONTHS_BETWEEN-------------- -------------- 0 .0322580651 row selected.SQL>SQL> select 2 datediff(month, date '2024-01-31', date '2024-02-29') as datediff_month, 3 months_between(date '2024-02-29', date '2024-01-31') as months_between 4 from dual;DATEDIFF_MONTH MONTHS_BETWEEN-------------- -------------- 1 11 row selected.
You can get quite different results from a MONTHS_BETWEEN function and a DATEDIFF function, even if you were to use ROUND or the CEIL/FLOOR functions on the MONTHS_BETWEEN. Be aware they are quite different implementations.
TL;DR: There is a decidedly distinct, demonstrably dramatic delta dividing DATEDIFF from its date-difference doppelgängers 🙂
Leave a Reply