If you have worked with
SQL for a while – you have most likely had a time where you wanted to show in a query the
difference between two dates.
It can be for example to calculate how long a person has been hired in your company, how old a person is today, etc.
In this tutorial we are going to look at some ways to do this in
Oracle SQL.
Before we move on
Before we dive into some of the different ways to find the
differences between two dates, I just wanted to let you know that there are many ways to do this. In this tutorial we are going to cover only a few of them.
Also, there are different ways to look at things… What I mean is: What if you have two
Oracle dates,
and you wanted to come up with a query that shows the months between
them. Would you then show the difference as i.e 2.7 months, or do you
want to say that there are two whole months between? Or, do you want to
show the number of months involved?
And, do you want to know i.e. just the
months between the dates, or do you want to show how many years there are between the dates too?
This seems pretty elementary, but I would suggest that before you actually start using the different
Oracle date functions,
you ask yourself exactly what result you would want first. If you do
not know up front what your result should be presented, you might end up
with some complications later, since some of the arithmetic can be
tricky to tweak afterwards.
Anyway…let’s get on with it.
The use of TRUNC with dates
Ok,
before we look at the first example I also would like to mention:
Remember the time part of your date field, if it has one. Many times an
order date in an order table will have a date with a time on it.
Depending on what method you are using to calculate any differences
between your two dates, you might end up with the wrong result.
Let me explain.
The time part of a TRUNC-ed date
1 | SQL> SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS AM') right_now_trunced |
2 | 2 ,TRUNC(SYSDATE) today |
3 | 3 ,TO_CHAR(TRUNC(SYSDATE), 'MM/DD/YYYY HH:MI:SS AM') right_now_trunced |
7 | RIGHT_NOW_TRUNCED TODAY RIGHT_NOW_TRUNCED |
9 | 01/31/2012 06:24:57 PM 1/31/2012 01/31/2012 12:00:00 AM |
In the example above you see that if we do a
TRUNC
on a date that has a time part on it, you will only see the date part
of your date field. If you show you show the time part of a date value
that you have used
TRUNC on, you will get a time part
of “12:00:00 AM”. All dates that do not have a time part specified will
by Oracle be thought about as midnight (“12:00:00 AM) that day.
1 | SQL> SELECT TO_DATE('20120125 11:00:00 AM', 'YYYYMMDD HH:MI:SS AM') |
2 | 2 - TO_DATE('20120120 11:00:00 AM', 'YYYYMMDD HH:MI:SS AM') day_diff |
9 | SQL> SELECT TO_DATE('20120125 10:00:00 AM', 'YYYYMMDD HH:MI:SS AM') |
10 | 2 - TO_DATE('20120120 11:00:00 AM', 'YYYYMMDD HH:MI:SS AM') day_diff |
17 | SQL> SELECT TO_DATE('20120125 11:00:00 AM', 'YYYYMMDD HH:MI:SS AM') |
18 | 2 - TO_DATE('20120120 10:00:00 AM', 'YYYYMMDD HH:MI:SS AM') day_diff |
If
you i.e. are doing calculations where the exact number of days has to
be totally correct, my suggestion is to always TRUNC all dates so that
your dates will be all looked at as if they were at midnight that day.
You will then always receive a result that is a whole number…
NOTE: I am going to discuss this in a later post, but be careful when you use the TRUNC function on dates in your function.
If you i.e. have a WHERE statement similar to this ….
1 | WHERE TRUNC(o.order_date) BETWEEN SYSDATE - 30 AND SYSDATE |
… chances are very high that you will get an explain plan of your query showing a full tablescan on your order table..
Just mentioning it….
Date difference examples
Alrighty
then…let’s look of some different examples of date difference
functionality. Please note that I will be using dates in these samples
that do not have a time part set.
Example # 1: How to get the number of days between two dates
The simplest form of showing this is using functionality we have already shown:
1 | SQL> SELECT TO_DATE('20120125', 'YYYYMMDD') - TO_DATE('20120120', 'YYYYMMDD') day_diff |
Ok, that was a bit too simple..
Example # 2: How to get number of months between two dates
Using the MONTHS_BETWEEN function
1 | SQL> SELECT MONTHS_BETWEEN(TO_DATE('20120325', 'YYYYMMDD'), TO_DATE('20120101', 'YYYYMMDD')) num_months |
2 | 2 ,(TO_DATE('20120325', 'YYYYMMDD') - TO_DATE('20120101', 'YYYYMMDD')) diff_in_days |
In this example we used the built-in Oracle MONTHS_BETWEEN function, which is pretty convenient to have some times.
Using plain math to get the number of months between two dates
This
is not a method I would recommend, but if you want to calculate months
between two days, and you are using Biblical months..being 30 days per
month…it would work fine.
1 | SQL> SELECT TRUNC((TO_DATE('20120325', 'YYYYMMDD') - TO_DATE('20120101', 'YYYYMMDD')) / 30) num_months |
2 | 2 ,(TO_DATE('20120325', 'YYYYMMDD') - TO_DATE('20120101', 'YYYYMMDD')) diff_in_days |
Using the EXTRACT function
In this example we are going to use the EXTRACT function to find the the month number, and then do a subtraction.
1 | SQL> SELECT TO_DATE('20120101', 'YYYYMMDD') start_date |
2 | 2 ,TO_DATE('20120325', 'YYYYMMDD') end_date |
3 | 3 ,(TO_DATE('20120325', 'YYYYMMDD') - TO_DATE('20120101', 'YYYYMMDD')) diff_in_days |
4 | 4 ,EXTRACT(MONTH FROM TO_DATE('20120101', 'YYYYMMDD')) start_month |
5 | 5 ,EXTRACT(MONTH FROM TO_DATE('20120325', 'YYYYMMDD')) end_month |
6 | 6 ,(EXTRACT(MONTH FROM TO_DATE('20120325', 'YYYYMMDD')) - EXTRACT(MONTH FROM TO_DATE('20120101', 'YYYYMMDD'))) diff_in_months |
10 | START_DATE END_DATE DIFF_IN_DAYS START_MONTH END_MONTH DIFF_IN_MONTHS |
12 | 1/1/2012 3/25/2012 84 1 3 2 |
But, what if there are different years on our two dates…?
Example # 3: How to get the number of years and months between two dates
One way to do this is actually to use the MONTHS_BETWEEN function like this:
1 | SQL> SELECT TRUNC(MONTHS_BETWEEN(TO_DATE('20120325', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD'))) |
2 | 2 ,(TO_DATE('20120325', 'YYYYMMDD') - TO_DATE('20100101', 'YYYYMMDD')) diff_in_days |
6 | TRUNC(MONTHS_BETWEEN(TO_DATE(' DIFF_IN_DAYS |
But, if we want to show the number of years as well, we can do something like this:
1 | SQL> SELECT (TO_DATE('20120525', 'YYYYMMDD') - TO_DATE('20100101', 'YYYYMMDD')) diff_in_days |
2 | 2 ,TRUNC(MONTHS_BETWEEN(TO_DATE('20120525', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD'))) |
3 | 3 ,TRUNC(TRUNC(MONTHS_BETWEEN(TO_DATE('20120525', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD'))) / 12) num_years |
4 | 4 ,MOD(TRUNC(MONTHS_BETWEEN(TO_DATE('20120525', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD'))), 12) num_months |
8 | DIFF_IN_DAYS TRUNC(MONTHS_BETWEEN(TO_DATE(' NUM_YEARS NUM_MONTHS |
Another way to do show the number of years and months could be something like this:
1 | SQL> SELECT TO_DATE('20100101', 'YYYYMMDD') start_date |
2 | 2 ,TO_DATE('20120525', 'YYYYMMDD') end_date |
3 | 3 ,(TO_DATE('20120525', 'YYYYMMDD') - TO_DATE('20100101', 'YYYYMMDD')) diff_in_days |
4 | 4 ,EXTRACT(YEAR FROM TO_DATE('20100101', 'YYYYMMDD')) start_month |
5 | 5 ,EXTRACT(YEAR FROM TO_DATE('20120525', 'YYYYMMDD')) end_month |
6 | 6 ,EXTRACT(MONTH FROM TO_DATE('20100101', 'YYYYMMDD')) start_month |
7 | 7 ,EXTRACT(MONTH FROM TO_DATE('20120525', 'YYYYMMDD')) end_month |
8 | 8 ,(EXTRACT(YEAR FROM TO_DATE('20120525', 'YYYYMMDD')) - EXTRACT(YEAR FROM TO_DATE('20100101', 'YYYYMMDD'))) diff_in_years |
9 | 9 ,(EXTRACT(MONTH FROM TO_DATE('20120525', 'YYYYMMDD')) - EXTRACT(MONTH FROM TO_DATE('20100101', 'YYYYMMDD'))) diff_in_months |
13 | START_DATE END_DATE DIFF_IN_DAYS START_MONTH END_MONTH START_MONTH END_MONTH DIFF_IN_YEARS DIFF_IN_MONTHS |
15 | 1/1/2010 5/25/2012 875 2010 2012 1 5 2 4 |
Example # 4: How to get number of years and months between two dates using INTERVAL datatypes
The
INTERVAL data types were introduced in Oracle 9i. There are two of them, but in this example we are going to use the one declared as “
INTERVAL YEAR TO MONTH“.
An example of usage of this in an Oracle SQL statement can be the following:
1 | SQL> SELECT x.start_date |
3 | 3 ,(x.end_date - x.start_date) diff_in_days |
4 | 4 ,(x.end_date - x.start_date) YEAR TO MONTH diff_in_months |
6 | 6 SELECT TO_DATE('20100101', 'YYYYMMDD') start_date |
7 | 7 ,TO_DATE('20120515', 'YYYYMMDD') end_date |
12 | START_DATE END_DATE DIFF_IN_DAYS DIFF_IN_MONTHS |
14 | 1/1/2010 5/15/2012 865 +02-04 |
As
you can see, the last field shows the result in the format “+02-00″. I
am NOT going to go into the detailed usage of this data type, but just
know that you can set i.e. the precision of the number of digits you
would want to use…like if you only want to show the months, and not
year…you can specify (i.e.) that you want to show the months as three
digits. The default is 2.
If you want to extract the year and month values above, you can i.e. do like this:
1 | SQL> SELECT y.start_date |
5 | 5 ,EXTRACT(YEAR FROM diff_in_interval) num_years |
6 | 6 ,EXTRACT(MONTH FROM diff_in_interval) num_months |
10 | 10 ,(x.end_date - x.start_date) diff_in_days |
11 | 11 ,(x.end_date - x.start_date) YEAR TO MONTH diff_in_interval |
13 | 13 SELECT TO_DATE('20100101', 'YYYYMMDD') start_date |
14 | 14 ,TO_DATE('20120515', 'YYYYMMDD') end_date |
20 | START_DATE END_DATE DIFF_IN_DAYS DIFF_IN_INTERVAL NUM_YEARS NUM_MONTHS |
22 | 1/1/2010 5/15/2012 865 +02-04 2 4 |
I
have to admit that I have actually never used the INTERVAL data types,
and to be honest there is a logic with this data type that I am not sure
if I totally understand…or have thought through yet…
If you look at the sample above, the end date is
TO_DATE(’20120515′, ‘YYYYMMDD’). What if we change the end date to i.e. TO_DATE(’20120516′, ‘YYYYMMDD’)…one day later?
1 | SQL> SELECT y.start_date |
5 | 5 ,EXTRACT(YEAR FROM diff_in_interval) num_years |
6 | 6 ,EXTRACT(MONTH FROM diff_in_interval) num_months |
10 | 10 ,(x.end_date - x.start_date) diff_in_days |
11 | 11 ,(x.end_date - x.start_date) YEAR TO MONTH diff_in_interval |
13 | 13 SELECT TO_DATE('20100101', 'YYYYMMDD') start_date |
14 | 14 ,TO_DATE('20120516', 'YYYYMMDD') end_date |
20 | START_DATE END_DATE DIFF_IN_DAYS DIFF_IN_INTERVAL NUM_YEARS NUM_MONTHS |
22 | 1/1/2010 5/16/2012 866 +02-05 2 5 |
You
will then see that it suddenly shows an extra month. I am not sure
exactly how the logic is meant to work, but it looks like there is a
difference “in the middle” of the month. If you have any explanation of
this, your contribution is welcomed.
In conclusion
As
you have seen, using Oracle date arithmetic, there are many ways to get
your result. In this tutorial I have shown some of them, and if you
have any additions to the examples..: Sharing is good.
I hope you enjoyed this tutorial, and that it helped you in your work.
If you have worked with SQL for a while – you have most likely had a time where you wanted to show in a query the difference between two dates.
No comments :
Post a Comment