Tuesday, June 18, 2013
Get the latest!
You are here: Home / Tutorials / How To Calculate Difference Between Dates in Oracle SQL
How To Calculate Difference Between Dates in Oracle SQL

How To Calculate Difference Between Dates in Oracle SQL

Posted by: TJ Abrahamsen February 26, 2012 in Tutorials 12 Comments

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

SQL> SELECT	TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS AM') right_now_trunced
  2  		,TRUNC(SYSDATE) today
  3  		,TO_CHAR(TRUNC(SYSDATE), 'MM/DD/YYYY HH:MI:SS AM') right_now_trunced
  4  FROM	dual
  5  /

RIGHT_NOW_TRUNCED      TODAY       RIGHT_NOW_TRUNCED
---------------------- ----------- ----------------------
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.

SQL> SELECT	TO_DATE('20120125 11:00:00 AM', 'YYYYMMDD HH:MI:SS AM')
  2  		- TO_DATE('20120120 11:00:00 AM', 'YYYYMMDD HH:MI:SS AM') day_diff
  3  FROM	dual
  4  /

  DAY_DIFF
----------
         5
SQL> SELECT	TO_DATE('20120125 10:00:00 AM', 'YYYYMMDD HH:MI:SS AM')
  2  		- TO_DATE('20120120 11:00:00 AM', 'YYYYMMDD HH:MI:SS AM') day_diff
  3  FROM	dual
  4  /

  DAY_DIFF
----------
4.95833333
SQL> SELECT	TO_DATE('20120125 11:00:00 AM', 'YYYYMMDD HH:MI:SS AM')
  2  		- TO_DATE('20120120 10:00:00 AM', 'YYYYMMDD HH:MI:SS AM') day_diff
  3  FROM	dual
  4  /

  DAY_DIFF
----------
5.04166666

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 ….

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:

SQL> SELECT	TO_DATE('20120125', 'YYYYMMDD') - TO_DATE('20120120', 'YYYYMMDD') day_diff
  2  FROM	dual
  3  /

  DAY_DIFF
----------
         5

Ok, that was a bit too simple..

Example # 2: How to get number of months between two dates

Using the MONTHS_BETWEEN function

SQL> SELECT	MONTHS_BETWEEN(TO_DATE('20120325', 'YYYYMMDD'), TO_DATE('20120101', 'YYYYMMDD')) num_months
  2  		,(TO_DATE('20120325', 'YYYYMMDD') - TO_DATE('20120101', 'YYYYMMDD')) diff_in_days
  3  FROM	dual
  4  /

NUM_MONTHS DIFF_IN_DAYS
---------- ------------
2.77419354           84

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.

SQL> SELECT	TRUNC((TO_DATE('20120325', 'YYYYMMDD') - TO_DATE('20120101', 'YYYYMMDD')) / 30) num_months
  2  		,(TO_DATE('20120325', 'YYYYMMDD') - TO_DATE('20120101', 'YYYYMMDD')) diff_in_days
  3  FROM	dual
  4  /

NUM_MONTHS DIFF_IN_DAYS
---------- ------------
         2           84

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.

 

SQL> SELECT	TO_DATE('20120101', 'YYYYMMDD') start_date
  2  		,TO_DATE('20120325', 'YYYYMMDD') end_date
  3  		,(TO_DATE('20120325', 'YYYYMMDD') - TO_DATE('20120101', 'YYYYMMDD')) diff_in_days
  4          ,EXTRACT(MONTH FROM TO_DATE('20120101', 'YYYYMMDD')) start_month
  5          ,EXTRACT(MONTH FROM TO_DATE('20120325', 'YYYYMMDD')) end_month
  6          ,(EXTRACT(MONTH FROM TO_DATE('20120325', 'YYYYMMDD')) - EXTRACT(MONTH FROM TO_DATE('20120101', 'YYYYMMDD'))) diff_in_months
  7  FROM	dual
  8  /

START_DATE  END_DATE    DIFF_IN_DAYS START_MONTH  END_MONTH DIFF_IN_MONTHS
----------- ----------- ------------ ----------- ---------- --------------
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:

SQL> SELECT	TRUNC(MONTHS_BETWEEN(TO_DATE('20120325', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD')))
  2  		,(TO_DATE('20120325', 'YYYYMMDD') - TO_DATE('20100101', 'YYYYMMDD')) diff_in_days
  3  FROM	dual
  4  /

TRUNC(MONTHS_BETWEEN(TO_DATE(' DIFF_IN_DAYS
------------------------------ ------------
                            26          814

But, if we want to show the number of years as well, we can do something like this:

SQL> SELECT	(TO_DATE('20120525', 'YYYYMMDD') - TO_DATE('20100101', 'YYYYMMDD')) diff_in_days
  2  		,TRUNC(MONTHS_BETWEEN(TO_DATE('20120525', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD')))
  3  		,TRUNC(TRUNC(MONTHS_BETWEEN(TO_DATE('20120525', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD'))) / 12) num_years
  4  		,MOD(TRUNC(MONTHS_BETWEEN(TO_DATE('20120525', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD'))), 12) num_months
  5  FROM	dual
  6  /

DIFF_IN_DAYS TRUNC(MONTHS_BETWEEN(TO_DATE('  NUM_YEARS NUM_MONTHS
------------ ------------------------------ ---------- ----------
         875                             28          2          4

Another way to do show the number of years and months could be something like this:

SQL> SELECT	TO_DATE('20100101', 'YYYYMMDD') start_date
  2  		,TO_DATE('20120525', 'YYYYMMDD') end_date
  3  		,(TO_DATE('20120525', 'YYYYMMDD') - TO_DATE('20100101', 'YYYYMMDD')) diff_in_days
  4          ,EXTRACT(YEAR FROM TO_DATE('20100101', 'YYYYMMDD')) start_month
  5          ,EXTRACT(YEAR FROM TO_DATE('20120525', 'YYYYMMDD')) end_month
  6          ,EXTRACT(MONTH FROM TO_DATE('20100101', 'YYYYMMDD')) start_month
  7          ,EXTRACT(MONTH FROM TO_DATE('20120525', 'YYYYMMDD')) end_month
  8          ,(EXTRACT(YEAR FROM TO_DATE('20120525', 'YYYYMMDD')) - EXTRACT(YEAR FROM TO_DATE('20100101', 'YYYYMMDD'))) diff_in_years
  9          ,(EXTRACT(MONTH FROM TO_DATE('20120525', 'YYYYMMDD')) - EXTRACT(MONTH FROM TO_DATE('20100101', 'YYYYMMDD'))) diff_in_months
 10  FROM	dual
 11  /

START_DATE  END_DATE    DIFF_IN_DAYS START_MONTH  END_MONTH START_MONTH  END_MONTH DIFF_IN_YEARS DIFF_IN_MONTHS
----------- ----------- ------------ ----------- ---------- ----------- ---------- ------------- --------------
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:

SQL> SELECT	x.start_date
  2          ,x.end_date
  3          ,(x.end_date - x.start_date) diff_in_days
  4          ,(x.end_date - x.start_date) YEAR TO MONTH diff_in_months
  5  FROM	(
  6              SELECT	TO_DATE('20100101', 'YYYYMMDD') start_date
  7                      ,TO_DATE('20120515', 'YYYYMMDD') end_date
  8              FROM	dual
  9          ) x
 10  /

START_DATE  END_DATE    DIFF_IN_DAYS DIFF_IN_MONTHS
----------- ----------- ------------ ---------------
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:

SQL> SELECT	y.start_date
  2          ,y.end_date
  3          ,y.diff_in_days
  4          ,y.diff_in_interval
  5          ,EXTRACT(YEAR FROM diff_in_interval) num_years
  6          ,EXTRACT(MONTH FROM diff_in_interval) num_months
  7  FROM	(
  8              SELECT	x.start_date
  9                      ,x.end_date
 10  			        ,(x.end_date - x.start_date) diff_in_days
 11                      ,(x.end_date - x.start_date) YEAR TO MONTH diff_in_interval
 12              FROM	(
 13                          SELECT	TO_DATE('20100101', 'YYYYMMDD') start_date
 14                                  ,TO_DATE('20120515', 'YYYYMMDD') end_date
 15                          FROM	dual
 16                      ) x
 17  		) y
 18  /

START_DATE  END_DATE    DIFF_IN_DAYS DIFF_IN_INTERVAL NUM_YEARS  NUM_MONTHS
----------- ----------- ------------ ---------------- ---------- ----------
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?

SQL> SELECT	y.start_date
  2          ,y.end_date
  3          ,y.diff_in_days
  4          ,y.diff_in_interval
  5          ,EXTRACT(YEAR FROM diff_in_interval) num_years
  6          ,EXTRACT(MONTH FROM diff_in_interval) num_months
  7  FROM	(
  8              SELECT	x.start_date
  9                      ,x.end_date
 10  			        ,(x.end_date - x.start_date) diff_in_days
 11                      ,(x.end_date - x.start_date) YEAR TO MONTH diff_in_interval
 12              FROM	(
 13                          SELECT	TO_DATE('20100101', 'YYYYMMDD') start_date
 14                                  ,TO_DATE('20120516', 'YYYYMMDD') end_date
 15                          FROM	dual
 16                      ) x
 17  		) y
 18  /

START_DATE  END_DATE    DIFF_IN_DAYS DIFF_IN_INTERVAL   NUM_YEARS  NUM_MONTHS
----------- ----------- ------------ ------------------ ---------- ----------
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.

 

Tagged with:

About TJ Abrahamsen

TJ Abrahamsen is the founder and author of OracleTuts. He has worked with Oracle since 1997. His expertise is with Oracle SQL and PL/SQL. He loves teaching others, and like to share his thoughts, ideas, and experience on his blog.
  • Singh

    Oracle support Mathematical Subtract ‘-’ operator on Data datatype. You may directly put in select clause following statement: 
    to_char (s.last_upd – s.created, ’999999D99′)

    {Links moderated away from comment}

    • http://oracletuts.net TJ Abrahamsen

       Hello Singh -

      Thank you for your input. This is basically what is used in the three first examples. :-)

      ~ TJ

  • Gopi

    Awesome TJ….

  • Tebogo Makhubela

    thank you so much for the tutorial, it really help. you are making a difference for many of the junior sql developers like me.

    here is what i did using this tutorial:

    with H_ORDERS as
    (select
    ORDER_NO,
    CUSTOMER,
    STATUS,
    AUDIT_DATE
    from EPPIX_OPAUDM
    )
    select
    order_no,
    customer,
    case
    when to_date(“Back Orders”,’DD/MON/YY’) is not null then
    case
    when to_date(“Deleted Orders”,’DD/MON/YY’) is not null then
    0
    when to_date(“Back Orders”,’DD/MON/YY’) to_date(“Invoiced Orders”,’DD/MON/YY’) then
    (to_date(“Back Orders”,’DD/MON/YY’) – to_date(“Invoiced Orders”,’DD/MON/YY’))
    when to_date(“Back Orders”,’DD/MON/YY’) = to_date(“Invoiced Orders”,’DD/MON/YY’) then
    0
    end
    else
    case
    when to_date(“Awaiting Despatch”,’DD/MON/YY’) is not null then
    case
    when to_date(“Deleted Orders”,’DD/MON/YY’) is not null then
    0
    when to_date(“Awaiting Despatch”,’DD/MON/YY’) to_date(“Invoiced Orders”,’DD/MON/YY’) then
    (to_date(“Awaiting Despatch”,’DD/MON/YY’) – to_date(“Invoiced Orders”,’DD/MON/YY’))
    when to_date(“Awaiting Despatch”,’DD/MON/YY’) = to_date(“Invoiced Orders”,’DD/MON/YY’) then
    0
    end
    end
    end date_dif,
    nvl(to_char(“Forward Orders”,’DD.MON.YY’),’-') “Forward Orders”,
    nvl(to_char(“Credit Stopped Orders”,’DD.MON.YY’),’-') “Credit Stopped Orders”,
    nvl(to_char(“Credit Stop/Back orders”,’DD.MON.YY’),’-') “Credit Stop/Back orders”,
    nvl(to_char(“Back Orders”,’DD.MON.YY’),’-') “Back Orders”,
    nvl(to_char(“Awaiting Despatch”,’DD.MON.YY’),’-') “Awaiting Despatch”,
    nvl(to_char(“Despatch Notes Printed”,’DD.MON.YY’),’-') “Despatch Notes Printed”,
    nvl(to_char(“Despatched Orders”,’DD.MON.YY’),’-') “Despatched Orders”,
    nvl(to_char(“Invoiced Orders”,’DD.MON.YY’),’-') “Invoiced Orders”,
    nvl(to_char(“Deleted Orders”,’DD.MON.YY’),’-') “Deleted Orders”
    from H_ORDERS
    PIVOT
    (
    max(AUDIT_DATE)
    for STATUS
    in
    (
    1 as “Forward Orders”,
    2 as “Credit Stopped Orders”,
    3 as “Credit Stop/Back orders”,
    4 as “Back Orders”,
    5 as “Awaiting Despatch”,
    6 as “Despatch Notes Printed”,
    7 as “Despatched Orders”,
    8 as “Invoiced Orders”,
    9 as “Deleted Orders”
    )
    )

    • http://oracletuts.net TJ Abrahamsen

      Hey there Tebogo -

      I am glad I could help. Thank you for sharing.

      ~ TJ

  • heiba

    TJ
    I am struggling with the script below, I am familiar with SQL 2005 but I need to convert this script to Oracle. Please Help.. Thanks

    if object_id (‘v_PlanSIVsActual’,'V’) is not null
    drop view v_PlanSIVsActual
    go
    create view v_PlanSIVsActual as
    select
    (select ‘Plan SI Vs Actual’) as title,
    (select cast(count(*) as float)
    from workorder
    where status in (‘CLOSE’,'WRKDN’,'COMP’) and worktype =’SI’
    and datepart(month, reportdate) = datepart(month, getdate())
    and datepart(year, reportdate) = datepart(year, getdate()))
    /
    (select cast(count (*) +1 as float) from workorder
    where worktype =’SI’
    and datepart(month, reportdate) = datepart(month, getdate())
    and datepart(year, reportdate) = datepart(year, getdate())) *100 as ThisMonth,

    (select cast(count(*) as float)
    from workorder
    where status in (‘CLOSE’,'WRKDN’,'COMP’) and worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-1, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-1, getdate())))
    /
    (select cast(count (*) +1 as float) from workorder
    where worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-1, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-1, getdate()))) *100 as OneMonthAgo,

    (select cast(count(*) as float)
    from workorder
    where status in (‘CLOSE’,'WRKDN’,'COMP’) and worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-2, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-2, getdate())))
    /
    (select cast(count (*) +1 as float) from workorder
    where worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-2, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-2, getdate()))) *100 as TwoMonthAgo,
    (select cast(count(*) as float)
    from workorder
    where status in (‘CLOSE’,'WRKDN’,'COMP’) and worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-3, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-3, getdate())))
    /
    (select cast(count (*) +1 as float) from workorder
    where worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-3, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-3, getdate()))) *100 as ThreeMonthAgo,

    (select cast(count(*) as float)
    from workorder
    where status in (‘CLOSE’,'WRKDN’,'COMP’) and worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-4, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-4, getdate())))
    /
    (select cast(count (*) +1 as float) from workorder
    where worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-4, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-4, getdate()))) *100 as FourMonthAgo,

    (select cast(count(*) as float)
    from workorder
    where status in (‘CLOSE’,'WRKDN’,'COMP’) and worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-5, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-5, getdate())))
    /
    (select cast(count (*) +1 as float) from workorder
    where worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-5, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-5, getdate()))) *100 as FiveMonthAgo,

    (select cast(count(*) as float)
    from workorder
    where status in (‘CLOSE’,'WRKDN’,'COMP’) and worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-6, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-6, getdate())))
    /
    (select cast(count (*) +1 as float) from workorder
    where worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-6, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-6, getdate()))) *100 as SixMonthAgo,

    (select cast(count(*) as float)
    from workorder
    where status in (‘CLOSE’,'WRKDN’,'COMP’) and worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-7, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-7, getdate())))
    /
    (select cast(count (*) +1 as float) from workorder
    where worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-7, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-7, getdate()))) *100 as SevenMonthAgo,

    (select cast(count(*) as float)
    from workorder
    where status in (‘CLOSE’,'WRKDN’,'COMP’) and worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-8, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-8, getdate())))
    /
    (select cast(count (*) +1 as float) from workorder
    where worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-8, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-8, getdate()))) *100 as EightMonthAgo,

    (select cast(count(*) as float)
    from workorder
    where status in (‘CLOSE’,'WRKDN’,'COMP’) and worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-9, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-9, getdate())))
    /
    (select cast(count (*) +1 as float) from workorder
    where worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-9, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-9, getdate()))) *100 as NineMonthAgo,

    (select cast(count(*) as float)
    from workorder
    where status in (‘CLOSE’,'WRKDN’,'COMP’) and worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-10, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-10, getdate())))
    /
    (select cast(count (*) +1 as float) from workorder
    where worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-10, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-10, getdate()))) *100 as TenMonthAgo,

    (select cast(count(*) as float)
    from workorder
    where status in (‘CLOSE’,'WRKDN’,'COMP’) and worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-11, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-11, getdate())))
    /
    (select cast(count (*) +1 as float) from workorder
    where worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-11, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-11, getdate()))) *100 as ElevenMonthAgo,

    (select cast(count(*) as float)
    from workorder
    where status in (‘CLOSE’,'WRKDN’,'COMP’) and worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-11, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-11, getdate())))
    /
    (select cast(count (*) +1 as float) from workorder
    where worktype =’SI’
    and datepart(month, reportdate) = datepart(month, dateadd(month,-11, getdate()))
    and datepart(year, reportdate) = datepart(year, dateadd(month,-11, getdate()))) *100 as TwelveMonthAgo

    from dummy_table

    select * from v_PlanSIVsActual

    • http://oracletuts.net TJ Abrahamsen

      Hi heiba -

      Thank you for your comment. I currently have a few deadlines in my own work world, so I would not be able to look at it right now…especially since your view/script is very long.
      But, a few pointers:
      1. You seem to have a lot of “repetive” similar queries. Maybe you should look into using a WITH statement in Oracle SQL..? Then you can actually reduce your numbers of requests to the database drastically.
      2. As you can see in my tutorial, I describe the use of EXTRACT. That seems to be what you should be using for your “datepart” pieces.
      3. getdate() in Oracle SQL is SYSDATE, or TRUNC(SYSDATE) to get without timepart of the current date/time.

      Hope this helps a bit.

      ~ TJ

  • heiba

    Hi Tom,

    Thanks for your Hint, however the script work ok but the extract function I am not sure if this is correct as the script running to bring data from this month as(This Month) and previous month as (One month ago) and previouse and so on.

    select ‘RW_CM open’,
    (select count(*) from workorder
    where status not in(‘CLOSE’,'COMP’) and siteid = ‘AIG’ and worktype in (‘RW’,'CM’)
    and extract (month from to_date(reportdate, ‘DD-MON-RR’)) = extract (month from to_date(reportdate, ‘DD-MON-RR’))
    and extract (year from to_date(reportdate, ‘DD-MON-RR’)) = extract(year from to_date(reportdate, ‘DD-MON-RR’))) as This_Month,

    (select count(*) from workorder
    where status not in(‘CLOSE’,'COMP’) and siteid = ‘AIG’ and worktype in (‘RW’,'CM’)
    and extract (month from to_date(reportdate, ‘DD-MON-RR’)) = extract (month from to_date(reportdate, ‘DD-MON-RR’)-1)
    and extract (year from to_date(reportdate, ‘DD-MON-RR’)) = extract(year from to_date(reportdate, ‘DD-MON-RR’)-1)) as One_Month_Ago from dummy_table;

  • saqib

    Hi to All,

    It is the best and simple formula to calculate Years, Months, Days between two dates

    SELECT TRUNC (MONTHS_BETWEEN (:end_date, :start_date) / 12) YEARS,
    MOD (TRUNC (MONTHS_BETWEEN (:end_date, :start_date)), 12) MONTHS,
    ( TO_DATE (:end_date) – ADD_MONTHS (:start_date, TRUNC (MONTHS_BETWEEN (:end_date, :start_date)))) DAYS
    FROM DUAL;

    • http://oracletuts.net TJ Abrahamsen

      Hi Saqib. Thank you for your contribution.

      ~ TJ

  • http://moobin.net Joel A. Villarreal Bertoldi

    A really simple way to get the number of years between two dates:

    SELECT ABS(MONTHS_BETWEEN(’01-jan-2012′, ’01-jan-2020′)) / 12 FROM DUAL;
    >> 8

    • http://oracletuts.net TJ Abrahamsen

      Hey there Joel. Thank you for your contribution.

      ~ TJ

(c) 2013 oracletuts.net. Some rights reserved. Web Design by Exillum
OracleTuts is not affiliated with or endorsed by the Oracle® Corporation. This site is not officially sponsored, or approved by the Oracle® Corporation. The products and company mentioned on this website may be the trademark of their respective owners.
Scroll To Top