Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Prakash .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: June 18, 2013 - 2:31 pm UTC

Version:

Viewed 50K+ times! This question is

You Asked

I want to get difference between two dates say

(10-FEB-2000 - 02-JAN-1999)

and the result has to be displayed in following format
1 year, 1 month, 8 days




and we said...



See </code> http://asktom.oracle.com/Misc/DateDiff.html <code>for some more info.

We can do this with some date arithmetic as follows:


ops$tkyte@8i> create table t ( end_date date, start_date date );
Table created.

ops$tkyte@8i> insert into t values ( '10-FEB-2000', '02-JAN-1999' );
1 row created.

ops$tkyte@8i> insert into t values ( '02-FEB-2000', '10-JAN-1999' );
1 row created.

ops$tkyte@8i> select months_between( end_date, start_date ),
2 trunc( months_between( end_date, start_date ) /12 ) Years,
3 mod( trunc( months_between( end_date, start_date ) ), 12 ) months,
4 end_date - add_months(start_date,trunc( months_between( end_date, start_date ) )) days
5 from t
6 /

MONTHS_BETWEEN(END_DATE,START_DATE) YEARS MONTHS DAYS
----------------------------------- ---------- ---------- ----------
13.2580645 1 1 8
12.7419355 1 0 23



Rating

  (121 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Perfect

Kim Njeru, November 21, 2002 - 5:24 pm UTC

You did it again Tom!
Date calculations are something that most DBA's don't know enough about but should know more about.


I think there is a flaw in the solution

Jon, January 16, 2003 - 1:51 pm UTC

Tom, I think there is a problem with this solution, at least in my mind.  When trying to get the difference between 28-feb-1994 and 31-mar-1996, I get 2 years, 1 month and 0 days.  I happen to think that the answer should be 2 years, 1 month, and 3 days, but perhaps that is just definitional, as 28-feb-1994 and 31-mar-1996 are both the last day of the month.  But that got me to wondering – what would we get if we looked for the difference between 28-feb-1994 and 30-mar-1996.  The answer is 2 years, 1 month and MINUS 1 days.  Can a date difference be both positive and negative?  Similarly, 29-MAR yields –2 days and 28-MAR yields –1 day.  27-MAR correctly yields 2 years, 0 months and 27 days.

Here is the script to create the data:

drop table t1;
create table t ( end_date date, start_date date );
insert into t values ( '27-MAR-1996','28-FEB-1994' );
insert into t values ( '28-MAR-1996','28-FEB-1994' );
insert into t values ( '29-MAR-1996','28-FEB-1994' );
insert into t values ( '30-MAR-1996','28-FEB-1994' );
insert into t values ( '31-MAR-1996','28-FEB-1994' );

and here are the results:

SQL> select end_date, start_date ,
  2         trunc( months_between( end_date, start_date ) /12 ) Years,
  3         mod( trunc( months_between( end_date, start_date ) ), 12 ) months,
  4         end_date - add_months(start_date,trunc( months_between(end_date,start_date
  5   ) )) days
  6  from t
  7  /

END_DATE    START_DATE      YEARS    MONTHS      DAYS
----------- ----------- --------- --------- ---------
27-mar-1996 28-feb-1994         2         0        27
28-mar-1996 28-feb-1994         2         1        -3
29-mar-1996 28-feb-1994         2         1        -2
30-mar-1996 28-feb-1994         2         1        -1
31-mar-1996 28-feb-1994         2         1         0

Is there a better way to do this? 

Tom Kyte
January 16, 2003 - 7:53 pm UTC

cool -- needs a tad bit of work eh...

boundary values, gotta hate them...

easiest thing is to zero out negative days -- it is as valid as anything else. But if you figure out what you *want* returned, it should be easy to add it.

You can use a simple greatest( 0, end_date - .... ) to zero it out.



Difference Between Two Dates

Khawar Iqbal Pasha, September 02, 2003 - 2:48 am UTC

Accumulating your tips and some of my experience i've written this function. May it would be helpful for others.

create or replace function date_diff ( p_date1 DATE , p_date2 DATE)
return char
is

Years NUMBER;
months NUMBER;
days NUMBER;
day_fraction NUMBER;
hrs NUMBER;
mints NUMBER;
sec NUMBER;

begin

Years :=trunc( months_between( p_date2 , p_date1 ) /12 );
months:=mod( trunc( months_between( p_date2, p_date1 ) ), 12 );
days :=trunc(p_date2 - add_months(p_date1,trunc(months_between(p_date2,p_date1) )));

day_fraction:= (p_date2-p_date1)-trunc(p_date2-p_date1);

hrs :=trunc(day_fraction*24);
mints :=trunc((((day_fraction)*24)-(hrs))*60);
sec :=trunc(mod((p_date2-p_date1)*86400,60));

return(years||' Years '||months||' Months '||days||' Days '||hrs||' Hours '||mints||' Minutes '||sec||' Seconds');

end;
/


Tom Kyte
September 02, 2003 - 7:29 am UTC

Reverse function

A reader, September 10, 2003 - 7:37 am UTC

What if I have want to convert 31000003170010.6 seconds into yyyy/mm/dd hh:mi:ss





Tom Kyte
September 10, 2003 - 7:37 pm UTC

hmm, is that just a random number or what ?

Not a random number

A reader, September 11, 2003 - 10:04 am UTC

I was actually asked this question? My initial response, That's just insane. I realize it would approximately equate to 100,000 years.

But, using a more realistic number, say 1,728,100 seconds, is it possible ?




Tom Kyte
September 11, 2003 - 6:04 pm UTC

sure, assuming the seconds are "since some time".

eg: lets say that is a "C time" or "unix time" -- the number of seconds since 1/1/1970

ops$tkyte@ORA920> variable theTime number
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec :theTime := 1728100
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> SELECT to_char(
  2          new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 * :theTime,
  3          'GMT', 'EDT' ), 'dd-mon-yyyy hh24:mi:ss' )
  4  from dual;
 
TO_CHAR(NEW_TIME(TO_
--------------------
20-jan-1970 20:01:40
 
ops$tkyte@ORA920>


does that. 

Calculating the difference between fields values to days and hours and minutes

Jamil, December 20, 2003 - 3:39 am UTC

Dear Sir

I have this problem ,my program calculate the journey times and install it in to this fields as the following
Journey_day char(5)
Journey_hour char(5)
Journey_ Minutes char(5)

And this field have a fixed value for expected time consume ,as the following fields

Expected_day char(5)
Expected_hour char(5)
Expected_ Minutes char(5)

So I want to calculate the difference between the above fields , for example
If the above fields have this values as follow :
-------------------
Varying fields values
Journey_day = 0
Journey_hour = 23
Journey_ Minutes = 30
------------------
This fixed fields values
Expected_day = 1
Expected_hour = 10
Expected_ Minutes = 0

So I want to calculated the difference between the above fields And install it in this fields as follow :

Result_day = to_number(Journey_day - Expected_day)
Resul_hour = to_number(Journey_hour - Expected_hour)
Result_ Minutes =to_number(Journey_Minutes- Expected_ Minutes)
The above statements it will not give the Wright result .

The required thing how can I calculate the difference between the above fields so that I will get the correct result in to those fields Result_day , Resul_hour, and Result_ Minutes

Please help
Best regards

Jamil




Tom Kyte
December 20, 2003 - 10:22 am UTC

ops$tkyte@ORA920> select t.*,
  2         trunc( e-j ) "Dy",
  3             trunc( mod( (e-j)*24, 24 ) )  "Hr",
  4             trunc( mod( (e-j)*24*60, 60 ) )  "Mi"
  5    from (
  6  select t.*,
  7         trunc(sysdate,'y')+jd+jh/24+jm/24/60 j,
  8         trunc(sysdate,'y')+ed+eh/24+em/24/60 e
  9    from t
 10         ) t
 11  /
 
 JD  JH  JM  ED  EH  EM J        E         Dy  Hr  Mi
--- --- --- --- --- --- -------- -------- --- --- ---
  0  23  30   1  10   0 01 23:30 02 10:00   0  10  30
 

Use date arithmetic.  if you are using 9i, you can use an interval type instead of individual fields.

 

Calculating the difference between fields values to days and hours and minutes

Jamil, December 21, 2003 - 4:08 am UTC

Dear Sir ,
 Thank you very much for your valuable answer , that is what I want 
 but my data in tow tables and the table structure 
as follow :

SQL> descr transaction_file 
 Name                  Null?       Type
 -----------------------------------------
 TRANSACTION_CODE                  NUMBER
 B_CODE                            NUMBER
 JD                                CHAR(5)
 JH                                CHAR(5)
 JM                                CHAR(5)


SQL> descr city
 Name                Null?         Type
 -------------------------------------------
 CITY_CODE                         NUMBER
 ED                                CHAR(5)
 EH                                CHAR(5)
 EM                                CHAR(5)

And the selected data from both tables as follow :

Table transaction_file contains:

TRANSACTION_CODE     B_CODE      JD    JH    JM
-------------------------------------------------
  1                  2           0     23    30
  2                  2           1     17    21
  3                  1           0     2     0


Table city  contains:


CITY_CODE   ED    EH    EM
-------------------------------
 1          0     2     0
 2          1     10    0


so I want the select statement to select from the above
tow tables
and  give the number of days , hours ,and  minutes  
and the link between the tow tables as follow 
B_CODE = CITY_CODE  , for each transaction_code  
it  should calculate the result .

Notice:
 
I want to install the number of days ,number of hours 
and the number of minutes in a variable
So that I want to install the result in another table.

I try to do it like this, but it does not work  

line 12 truncated.
  1  select         trunc( e-j ) "Dy",
  2                 trunc( mod( (e-j)*24, 24 ) )  "Hr",
  3                 trunc( mod( (e-j)*24*60, 60 ) )  "Mi"
  4        from (
  5      select
  6             trunc(sysdate,'y')+jd+jh/24+jm/24/60 j,
  7             trunc(sysdate,'y')+ed+eh/24+em/24/60 e
  8         from city A
  9              WHERE A.CITY_CODE = B.B_CODE
 10              and   B.TRANSACTION_CODE =  B.TRANSACTION_CODE
 11*           ) TRANSACTION_FILE B INTO R_DAYS,R_HOURS,R_MINUTES
 12  /
          ) TRANSACTION_FILE B INTO R_DAYS,R_HOURS,R_MINUTES
                             *
ERROR at line 11:
ORA-00933: SQL command not properly ended


Tank you very much for your great help.


Best regards

Jamil






 

Tom Kyte
December 21, 2003 - 10:32 am UTC

lose the "into r_days,r_hours,r_minutes" first of all.

that only works in programatic environments.

it would be after the select list -- NOT at the end of the list.

actually -- your entire query is sort of "not really SQL at all"

but, your description -- if accurate -- makes this a pretty simple problem.  Just work with the data:

ops$tkyte@ORA920> select *
  2    from city c, transaction_file tf
  3   where c.city_code = tf.b_code
  4  /
 
 CITY_CODE         ED         EH         EM TRANSACTION_CODE     B_CODE         JD         JH         JM
---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------- ----------
         1          0          2          0                3          1          0          2          0
         2          1         10          0                1          2          0         23         30
         2          1         10          0                2          2          1         17         21
 


Now, as that is a multi-row result set there will be no "into" clause (that works with a row only)... so


ops$tkyte@ORA920> select transaction_code,
  2         trunc(sysdate,'y')+jd+jh/24+jm/24/60 j,
  3         trunc(sysdate,'y')+ed+eh/24+em/24/60 e
  4    from city c, transaction_file tf
  5   where c.city_code = tf.b_code
  6  /
 
TRANSACTION_CODE J                    E
---------------- -------------------- --------------------
               3 01-jan-2003 02:00:00 01-jan-2003 02:00:00
               1 01-jan-2003 23:30:00 02-jan-2003 10:00:00
               2 02-jan-2003 17:21:00 02-jan-2003 10:00:00

<b>but that points out an interesting anomoly in your data -- your end times are sometimes before the "starts", so, you'll get negative results:</b>

 
ops$tkyte@ORA920> select transaction_code,
  2         trunc( e-j ) "Dy",
  3         trunc( mod( (e-j)*24, 24 ) )  "Hr",
  4             trunc( mod( (e-j)*24*60, 60 ) )  "Mi"
  5    from (
  6  select transaction_code,
  7         trunc(sysdate,'y')+jd+jh/24+jm/24/60 j,
  8         trunc(sysdate,'y')+ed+eh/24+em/24/60 e
  9    from city c, transaction_file tf
 10   where c.city_code = tf.b_code
 11         )
 12  /
 
TRANSACTION_CODE         Dy         Hr         Mi
---------------- ---------- ---------- ----------
               3          0          0          0
               1          0         10         30
               2          0         -7        -21
 


 

Calculating the difference between fields values to days and hours and minutes

Jamil, December 22, 2003 - 6:12 am UTC

Dear Sir
As usual great response from Tom,Thank you very much for your answer and it works fine, that is actually what I
want.
Can you clarify these statements is not possible to do the calculation without this statements
trunc(sysdate,'y')


Best regards

Jamil


Tom Kyte
December 22, 2003 - 9:11 am UTC

you certainly can do it without trunc(sysdate,'y')


i used that cause printing out the dates looks "sensible" (they'll be offset from the first day of the year. so your duration of 5 days 10 hours 20 minutes would be 10:20 am on jan 5th -- that was sensible to me as I was developing the query, something I could understand. You could use sysdate or any date really -- but using the first of the year made debugging this easy)


Date difference in seconds

Tony, January 30, 2004 - 10:52 am UTC

Hi tom,

Two date columns have date and time stored.
How to find the date difference in seconds?
I want day, month, minute and years difference everyting in seconds.


Tom Kyte
January 30, 2004 - 7:26 pm UTC

select (dt1-dt2) * 24 * 60 * 60 from t;


dt1-dt2 gives diff in days (eg: 1.2323 days)

multiply by 24 = hours, another 60 = minutes, another 60 = seconds

Negative Seconds

A reader, March 09, 2004 - 4:01 pm UTC

Hi, TOm,

In your link:
</code> http://asktom.oracle.com/~tkyte/Misc/DateDiff.html <code>

Some of the results in SECs are negative, is it normal?

THanks,


Tom Kyte
March 09, 2004 - 10:31 pm UTC

do you have an example? (you do need to put the "bigger" date in first -- you can use greatest(a,b) where I used sysdate and least(a,b) where I used created)

Here is the example

A reader, March 11, 2004 - 9:47 am UTC

Hi,

Did I do something wrong?
select to_char( created, 'dd-mon-yyyy hh24:mi:ss' ),
trunc( sysdate-created ) "Dy",
trunc( mod( (sysdate-created)*24, 24 ) ) "Hr",
trunc( mod( (sysdate-created)*24*60, 60 ) ) "Mi",
trunc( mod( to_char(sysdate,'SSSSS')-to_char(created,'SSSSS'), 60 ) ) "Sec",
to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ),
sysdate-created "Tdy",
(sysdate-created)*24 "Thr",
(sysdate-created)*24*60 "Tmi",
(sysdate-created)*24*60*60 "Tsec"
from all_users
where rownum < 50

And the result:
TO_CHAR(CREATED,'DD- Dy Hr Mi Sec TO_CHAR(SYSDATE,'DD-
-------------------- ---------- ---------- ---------- ---------- --------------------
10-jun-2002 13:48:17 639 19 0 -55 11-mar-2004 08:48:22
10-jun-2002 13:48:18 639 19 0 -56 11-mar-2004 08:48:22
10-jun-2002 13:48:38 639 18 59 -16 11-mar-2004 08:48:22
01-may-2003 09:28:45 314 23 19 -23 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
03-apr-2003 13:59:51 342 18 48 -29 11-mar-2004 08:48:22
10-jun-2002 15:10:09 639 17 38 -47 11-mar-2004 08:48:22
10-jun-2002 15:10:11 639 17 38 -49 11-mar-2004 08:48:22
10-jun-2002 15:10:11 639 17 38 -49 11-mar-2004 08:48:22
10-jun-2002 15:17:16 639 17 31 -54 11-mar-2004 08:48:22
10-jun-2002 16:14:21 639 16 34 -59 11-mar-2004 08:48:22
10-jun-2002 16:14:21 639 16 34 -59 11-mar-2004 08:48:22
10-jun-2002 16:14:21 639 16 34 -59 11-mar-2004 08:48:22
10-jun-2002 16:14:21 639 16 34 -59 11-mar-2004 08:48:22
10-jun-2002 16:14:21 639 16 34 -59 11-mar-2004 08:48:22
10-jun-2002 16:14:21 639 16 34 -59 11-mar-2004 08:48:22
10-jun-2002 16:14:24 639 16 33 -2 11-mar-2004 08:48:22
10-jun-2002 16:14:24 639 16 33 -2 11-mar-2004 08:48:22
10-jun-2002 16:14:25 639 16 33 -3 11-mar-2004 08:48:22
10-jun-2002 16:14:25 639 16 33 -3 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:24 638 23 48 -2 11-mar-2004 08:48:22
11-jun-2002 08:59:25 638 23 48 -3 11-mar-2004 08:48:22
11-jun-2002 08:59:25 638 23 48 -3 11-mar-2004 08:48:22
11-jun-2002 08:59:25 638 23 48 -3 11-mar-2004 08:48:22
11-jun-2002 08:59:25 638 23 48 -3 11-mar-2004 08:48:22
11-jun-2002 08:59:25 638 23 48 -3 11-mar-2004 08:48:22
11-jun-2002 08:59:25 638 23 48 -3 11-mar-2004 08:48:22
11-jun-2002 08:59:25 638 23 48 -3 11-mar-2004 08:48:22
11-jun-2002 08:59:25 638 23 48 -3 11-mar-2004 08:48:22

Tom Kyte
March 11, 2004 - 1:48 pm UTC

looks like its the math 

trunc( mod( to_char(sysdate,'SSSSS')-to_char(created,'SSSSS'), 60 ) )


sssss is seconds past midnight.  that'll be negative if sysdate is "early" in the day

ops$tkyte@ORA9IR2> select to_char( d1, 'dd-mon-yyyy hh24:mi:ss' ),
  2         to_char( d2, 'dd-mon-yyyy hh24:mi:ss' ),
  3         trunc( d1-d2 ) "Dy",
  4         trunc( mod( (d1-d2)*24, 24 ) )  "Hr",
  5         trunc( mod( (d1-d2)*24*60, 60 ) )  "Mi",
  6   trunc( mod( to_char(d1,'SSSSS')-to_char(d2,'SSSSS'), 60 ) ) "Sec"
  7  from (select sysdate d1, sysdate-1+5/24/60/60 d2 from dual )
  8  /
 
TO_CHAR(D1,'DD-MON-Y TO_CHAR(D2,'DD-MON-Y    Dy      Hr         Mi        Sec
-------------------- -------------------- ----- ------- ---------- ----------
11-mar-2004 12:43:38 10-mar-2004 12:43:43     0      23         59         -5

quick fix, haven't tested all boundary conditions:

ops$tkyte@ORA9IR2> select d1s, d2s, to_char(d2+"Hr"/24+"Mi"/24/60+"Sec"/24/60/60,'dd-mon-yyyy hh24:mi:ss') new,
  2         "Dy", "Hr", "Mi", "Sec"
  3    from (
  4  select to_char( d1, 'dd-mon-yyyy hh24:mi:ss' ) d1s,
  5         to_char( d2, 'dd-mon-yyyy hh24:mi:ss' ) d2s,
  6             d2,
  7         trunc( d1-d2 ) "Dy",
  8         trunc( mod( (d1-d2)*24, 24 ) )  "Hr",
  9         trunc( mod( (d1-d2)*24*60, 60 ) )  "Mi",
 10         trunc( mod( (d1-d2)*24*60*60, 60 ) ) "Sec"
 11  from (select sysdate d1, sysdate-1+5/24/60/60 d2 from dual )
 12  )
 13  /
 
D1S                  D2S                  NEW                          Dy         Hr         Mi        Sec
-------------------- -------------------- -------------------- ---------- ---------- ---------- ----------
11-mar-2004 12:48:27 10-mar-2004 12:48:32 11-mar-2004 12:48:27          0         23         59         55



another would be to use decode( sign("Sec"), -1, 60+"Sec", "Sec" )

(can't believe no one has noticed that before!)

 

Once again to_char(date) rules!

andrew, March 12, 2004 - 3:06 pm UTC

SQL> column the_diff format a30
SQL> select -- method 1
  2    TO_CHAR (TRUNC (SYSDATE-CREATED), '000') || ' days ' ||
  3    TO_CHAR (TRUNC (SYSDATE) + MOD (SYSDATE-CREATED, 1),
  4          'HH24 "hrs" MI "min" SS "sec"') the_diff,
  5    -- method 2
  6    trunc(sysdate-created ) "Dy",
  7    trunc(mod((sysdate-created)*24, 24 ))  "Hr",
  8    trunc(mod((sysdate-created)*24*60, 60 ))  "Mi",
  9    trunc(mod(to_char(sysdate,'SSSSS')-to_char(created,'SSSSS'), 60)) "Sec"
 10  from all_users
 11  where rownum < 5;

THE_DIFF                               Dy         Hr         Mi        Sec
------------------------------ ---------- ---------- ---------- ----------
 967 days 21 hrs 37 min 16 sec        967         21         37        -44
 967 days 21 hrs 37 min 16 sec        967         21         37        -44
 967 days 21 hrs 36 min 57 sec        967         21         36         -3
 029 days 18 hrs 17 min 15 sec         29         18         17        -45 

expected DATE got DATE

Justin, April 25, 2004 - 8:04 am UTC

Tom,

This is strange.

This works fine:

wic_owner5@DEV> select sysdate - add_months(sysdate,12) from dual;

SYSDATE-ADD_MONTHS(SYSDATE,12)
==============================
-365

This doesn't with a funny error message.

What am I doing wrong?
I just want to update the value of the date field to 1 year in the past.

wic_owner5@DEV> update t069_person set BIRTH_DT = sysdate - add_months(sysdate,12) where t069_id =8748;
update t069_person set BIRTH_DT = sysdate - add_months(sysdate,12) where t069_id =8748
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got DATE

All similar attempts, other than sysdate - ### seem to not work.

wic_owner5@DEV> update loser set val = sysdate - (sysdate + 20);
update loser set val = sysdate - (sysdate + 20)
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got DATE

Thank you very much for your time.



Tom Kyte
April 26, 2004 - 5:46 am UTC

dateA - dateB = days between the two (a number).

if you want a field to be a year in the past, you

update t
set x = add_months(x,-12)
where ..........



or

set x = add_months(sysdate,-12)



thanks very much

Justin, April 26, 2004 - 11:11 am UTC

The error message threw me. Still a dumbo on my part.

Thanks!

months between

Pukula, August 02, 2004 - 8:24 am UTC

Hi

I want to find orders in one month in a year say July

I do

order_date between to_date('200407', 'YYYYMM') and to_date('200408', 'YYYYMM')

It´s wrong because I am including 20040801 00:00:00, I then do

order_date >= to_date('200407', 'YYYYMM')
and order_date < to_date('200408', 'YYYYMM')

I would like to know if there is a way to do this with between and not using HH:MI:SS?

Cheers

Tom Kyte
August 02, 2004 - 8:51 am UTC

order_date between to_date('200407', 'YYYYMM')
and to_date('200408', 'YYYYMM')-1/24/60/60


if you use timestamps instead of dates, same concept -- just be subtracting a smaller number.

How can I get a date back from Varchar2

Venkat, August 10, 2004 - 5:37 pm UTC

Hi Tom,
There is a table with dates stored as varchar2. They are derived using the conversion as follows in a PLSQL procedure:
to_char(<some_date>, 'YYYY-MM-DD HH24:MI:SSSSS').

Now, I want to convert this VARCHAR2 value as a date.

drop table t;

create table t ( ts varchar2(30) )
/

insert into t values ( '2004-07-30 07:07:25972' )
/

insert into t values ('2004-07-30 17:07:62891' )
/

select ts from t;

TS
----------------------
2004-07-30 07:07:25972
2004-07-30 17:07:62891

How do I convert the SSSSS component above into a date format.

When I try this, I get an error:
select ts, to_date(ts, 'yyyy-mm-dd hh24:mi:sssss') dt
from t
/

select ts, to_date(ts, 'yyyy-mm-dd hh24:mi:sssss') dt
*
ERROR at line 1:
ORA-01837: minutes of hour conflicts with seconds in day


encompass@tkd16> l
1* select * from nls_session_parameters
encompass@tkd16> /

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-YY
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

Regards,
Venkat

Tom Kyte
August 10, 2004 - 7:42 pm UTC

hmmm, did the people understand what sssss is?  that is "seconds past midnight"

they probably thought "hey, we are able to magically get hundredths of seconds" (wrong, not happening)

you'll have to trunc(that sssss part/60)

or substring out the hh:mi part.

ops$tkyte@ORA9IR2> select       to_date( substr( ts, 1, 10 ) || substr( ts, 18 ), 'yyyy-mm-ddsssss'     )
  2    from t;
 
TO_DATE(SUBSTR(TS,1,
--------------------
30-jul-2004 07:12:52
30-jul-2004 17:28:11


sigh, sigh, sigh, the old "store a date in a string" - what a bad bad horrible idea.  ranks up there in the top 5 things I *hate* (right next to number in a string) 

Hmmm..Minutes do not match

Venkat, August 11, 2004 - 1:39 pm UTC

Tom,
You said:
<
hmmm, did the people understand what sssss is? that is "seconds past midnight"
they probably thought "hey, we are able to magically get hundredths of seconds"
(wrong, not happening)
> - I guess that might have been the impression

Now, how do we explain the fact that the minutes stored in HH:MI is different from the minutes derived by converting the sssss component.

Here is the example:

select ts ,
to_char(
to_date( substr( ts, 1, 10 ) || substr( ts, 18 ),
'yyyy-mm-ddsssss'),
'yyyy-mm-dd hh24:mi:ss') ch_dt
from t

TS CH_DT
------------------------------ -------------------
2004-07-30 07:07:25972 2004-07-30 07:12:52
2004-07-30 17:07:62891 2004-07-30 17:28:11


See the minute component in the second column is different. Instead of 07, it is 12 and 28 in the first and second record respectively.
Hmmm....

I tried to do this to replicate the original date to varchar2 conversions.

insert into t
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SSSSS')
from dual
/

1 row created.

And then I ran the query again.

select ts ,
to_char(
to_date( substr( ts, 1, 10 ) || substr( ts, 18 ),
'yyyy-mm-ddsssss'),
'yyyy-mm-dd hh24:mi:ss') ch_dt
from t
/

TS CH_DT
------------------------------ -------------------
2004-07-30 07:07:25972 2004-07-30 07:12:52
2004-07-30 17:07:62891 2004-07-30 17:28:11
2004-08-11 12:24:44689 2004-08-11 12:24:49

The new record looks ok after the conversion but why are the original ones off by a few minutes?

Venkat

Tom Kyte
August 11, 2004 - 2:37 pm UTC

sorry -- but your numbers do not "jive" then. you have:

select ts ,
  to_char(
     to_date( substr( ts, 1, 10 ) || substr( ts, 18 ), 
              'yyyy-mm-ddsssss'),
     'yyyy-mm-dd hh24:mi:ss') ch_dt
 from  t

TS                             CH_DT
------------------------------ -------------------
2004-07-30 07:07:25972         2004-07-30 07:12:52
2004-07-30 17:07:62891         2004-07-30 17:28:11


now, if the format was really truly:

YYYY-MM-DD HH24:MI:SSSSS

then the sssss number does not compute -- do the math:

ops$tkyte@ORA9I> select 7*60*60+7*60, 7*60*60+8*60 from dual
  2  /
 
7*60*60+7*60 7*60*60+8*60
------------ ------------
       25620        25680
 
ops$tkyte@ORA9I> select 7*60*60+12*60+52 from dual
  2  /
 
7*60*60+12*60+52
----------------
           25972


25972 cannot be between 25620 and 25680


that 25972 cannot be at 07:07 -- math precludes it. 

Why minutes and seconds don´t check

Dalarid, August 29, 2004 - 9:52 pm UTC

The last record fails by 1 second. What's Wrong.

MOD.> select to_char( salida_plan, 'dd-mon-yyyy hh24:mi:ss' ) salida,
2 to_char( regreso_plan, 'dd-mon-yyyy hh24:mi:ss' ) regreso,
3 trunc( regreso_plan-salida_plan ) "Dy",
4 trunc( mod( (regreso_plan-salida_plan)*24, 24 ) ) "Hr",
5 trunc( mod( (regreso_plan-salida_plan)*24*60, 60 ) ) "Mi",
6 trunc( mod( (regreso_plan-salida_plan)*24*60*60, 60 ) ) "Sec"
7 from ordenes_embarque;

SALIDA REGRESO Dy Hr Mi Sec
-------------------- -------------------- ------- ------- ------- -------
27-aug-2004 12:14:00 27-aug-2004 15:40:00 0 3 26 0
27-aug-2004 11:27:00 27-aug-2004 14:25:00 0 2 58 0
27-aug-2004 14:55:00 27-aug-2004 17:33:00 0 2 38 0
27-aug-2004 14:00:00 27-aug-2004 18:09:00 0 4 9 0
27-aug-2004 14:00:00 27-aug-2004 17:52:00 0 3 52 0
27-aug-2004 14:00:00 27-aug-2004 16:13:00 0 2 13 0
27-aug-2004 16:43:00 27-aug-2004 17:43:00 0 1 0 0
27-aug-2004 14:00:00 27-aug-2004 17:38:00 0 3 38 0
27-aug-2004 10:42:00 27-aug-2004 11:44:00 0 1 1 59


Tom Kyte
August 30, 2004 - 8:11 am UTC

rounding error.

display months b/w a date range

Asim Naveed, September 01, 2004 - 3:44 am UTC

Hi,

I have a slightly different problem.

Create table t1 (d1 date not null, d2 date not null);
INSERT INTO T1
(d1, d2)
values
(
TO_DATE('04-JAN-2004','DD-MON-YYYY'),
TO_DATE('17-OCT-2004','DD-MON-YYYY')
)

I want to make a query that will display the month
names between d2 and d1, note that there can be more than
12 months b/w d2 and d1.
The prefered solutions is to do it with pure SQL and/or
built in functions but not user defined functions.
These month names can be
displayed in any of the follwing two ways

1- Concatenate all the month names and display it under
a new column, output like this

d1 d2 Months_between
--- --- ----------------
04-JAN-2004 17-OCT-2004 Jan,Feb,Mar,Apr...Oct
14-OCT-2003 12-dec-2003 Oct, Nov, Dec
.
.

2- The manth names can be displayed one on each row,
out put like this

d1 d2 Months_between
--- ---- ---------------
04-JAN-2004 17-OCT-2004 Jan
Feb
Mar
Apr
May
June
July
Aug
Sep
Oct

10-oct-2003 12-dec-2003 Oct
Nov
Dec
.
.
.
.
.
.
.




Tom Kyte
September 01, 2004 - 8:17 am UTC

ops$tkyte@ORA9IR2> select d1, d2, add_months(trunc(d1,'mm'),r)
  2    from t1, (select rownum-1 r
  3                from all_objects
  4               where rownum <= ( select max(months_between(d2,d1))+1 from t1) )
  5   where months_between(d2,d1)+1 >= r
  6  /
 
D1        D2        ADD_MONTH
--------- --------- ---------
04-JAN-04 17-OCT-04 01-JAN-04
04-JAN-04 17-OCT-04 01-FEB-04
04-JAN-04 17-OCT-04 01-MAR-04
04-JAN-04 17-OCT-04 01-APR-04
04-JAN-04 17-OCT-04 01-MAY-04
04-JAN-04 17-OCT-04 01-JUN-04
04-JAN-04 17-OCT-04 01-JUL-04
04-JAN-04 17-OCT-04 01-AUG-04
04-JAN-04 17-OCT-04 01-SEP-04
04-JAN-04 17-OCT-04 01-OCT-04
 
10 rows selected.



You just need a table to join to that has more rows than "max months between" 

display months b/w a date range

Asim Naveed, September 01, 2004 - 10:56 pm UTC

Wow, thats great, it almost solved my problem.

But I also want that d1,d2 values should not
repeat for every row (generated by joining). d1, d2
should only display once.

Also is there anyway I can display all month names
concatenated as I mentioned in output way no. 1. If not
possible with pure SQL and Builtin function, how is it
possible with User defined functions

Thanks alot for your help

Tom Kyte
September 02, 2004 - 8:02 am UTC

simple, instead of :

select d1, d2, ...

select decode( r, 0, d1 ), decode( r, 0, d2 ), ....


search for stragg for one concat method, or if you have a "reasonable" maximum, you can:



if you have a reasonable maximum (and you do, varchar2's are 4000 -- so you have a 1,000 max)

ops$tkyte@ORA9IR2>      select d1, d2,
  2                  rtrim(
  3              decode( sign( 0-months_between(d2,d1)), -1, to_char(add_months(d1,0),'mon')||',' )  ||
  4              decode( sign( 1-months_between(d2,d1)), -1, to_char(add_months(d1,1),'mon')||',' )  ||
  5              decode( sign( 2-months_between(d2,d1)), -1, to_char(add_months(d1,2),'mon')||',' )  ||
  6              decode( sign( 3-months_between(d2,d1)), -1, to_char(add_months(d1,3),'mon')||',' )  ||
  7              decode( sign( 4-months_between(d2,d1)), -1, to_char(add_months(d1,4),'mon')||',' )  ||
  8              decode( sign( 5-months_between(d2,d1)), -1, to_char(add_months(d1,5),'mon')||',' )  ||
  9              decode( sign( 6-months_between(d2,d1)), -1, to_char(add_months(d1,6),'mon')||',' )  ||
 10              decode( sign( 7-months_between(d2,d1)), -1, to_char(add_months(d1,7),'mon')||',' )  ||
 11              decode( sign( 8-months_between(d2,d1)), -1, to_char(add_months(d1,8),'mon')||',' )  ||
 12              decode( sign( 9-months_between(d2,d1)), -1, to_char(add_months(d1,9),'mon')||',' )  ||
 13              decode( sign(10-months_between(d2,d1)), -1, to_char(add_months(d1,10),'mon')||',' )  ||
.......
 14              decode( sign(111-months_between(d2,d1)), -1, to_char(add_months(d1,111),'mon')||',' ),',')  months
 15  from ( select to_date('04-jan-04') d1, to_date( '17-oct-04') d2 from dual )
 16  /
 
D1        D2        MONTHS
--------- --------- ------------------------------------------------
04-JAN-04 17-OCT-04 jan,feb,mar,apr,may,jun,jul,aug,sep,oct
 
 

Fiscal Year

Guna, October 21, 2004 - 7:46 pm UTC

Hi Tom, I have following requirments.

1 )I have a date column called pstart_date, I want to select a value like "pstart_date < (TODAY() – 4 months)"

2 )I want to create a view based on multiple columns from a table and a column called FY (not exists in table), this FY column should return like "If TODAY() BETWEEN October 1 AND December 31, THEN FY = [YEAR of TODAY()] ELSE FY = [YEAR of TODAY()] - 1 "

3) I have a column called pend_date, I want to select a value like "BETWEEN October 1, ([FY]-1) and September 20, [FY] "

Please help me.

Thanks in advance,
Guna



Tom Kyte
October 22, 2004 - 3:53 pm UTC

1) what does it mean to you to "select that" column?

probably:

select ....,
case
when pstart_date < add_months(sysdate,-4)
then 'Yes, it is older than 4 months'
else 'Nope, not older than 4 months'
end,
.....


you can use decode as well

decode( sign( pstart_date - add_months(sysdate,-4) ), -1, "yes, it is older...',
'nope, not older...' )


2) see #1, you should be able to figure that out now, case or decode....

3) see #1

Clarificatins..

A reader, October 26, 2004 - 2:25 pm UTC

Tom, Thanks for your response.One more question,

If today's date is between Sep 01 2004 and December 31 2004, then I need Year of today +1 (ie 2005) else I need year of today - 1(ie 2003).
=====================================
Here is my query.
SELECT decode( sign( sysdate - add_months(sysdate,+2) ), -1,)
(select extract(year from sysdate+365) from dual),(select extract(year from sysdate) from dual ) FY from dual
/

I'm getting missing expression error.

Thanks.

Tom Kyte
October 26, 2004 - 2:47 pm UTC

  1  select case when to_number(to_char(sysdate,'MM')) between 9 and 12
  2              then to_char(add_months(sysdate,12),'yyyy')
  3                     else to_char(sysdate,'yyyy')
  4             end
  5*  from dual
ops$tkyte@ORA9IR2> /
 
CASE
----
2005
 

Date

A reader, October 26, 2004 - 3:18 pm UTC

Thanks a lot Tom.Instead of just 2005 ,I need FY2005 as an output.Pls let me know.

Tom Kyte
October 26, 2004 - 3:28 pm UTC

use 'FY'||to_char(...,'yyyy') then.

Thank You !

A reader, October 26, 2004 - 3:38 pm UTC

Thank you very much Tom !

David Aldridge, October 26, 2004 - 4:38 pm UTC

>> use 'FY'||to_char(...,'yyyy') then

or to_char(...,'"FY"yyyy') of course. Not much difference here, but it's handy syntax to know if you need to generate something like "Y2004_M03_D07" with ...

to_char(...,'"Y"yyyy"_M"mm"_D"dd')

... instead of ...

'Y'||to_char(...,'yyyy')||'_M'||to_char(...,'mm')||'_D'||to_char(...,'dd')

Need Help !

A reader, October 27, 2004 - 8:02 pm UTC

SQL> create or replace view BUDGET_SPREADSHEET_VIEW as select g.grant_id,G.GRANTNUM,G.PROJSTART,g.pr
ojend, (select case when to_number(to_char(sysdate,'MM')) between 9 and 12 then 'FY'||to_char add_monthssysdate,12),'yyyy') else to_char sysdate,'yyyy')  end FY from dual) as FY,U.LAST_NAME, U.FIRST_NAME,CI.contact_lname,CI.contact_COMPANY,GB.DIRCOST,GB.INDCOST,GB.PERIODST,decode(gb.type,36,'Conn',
  39,'CptCont',
  38,'Supp',
  37,'Supp',
  35,'Cont',
 40,'New',
 145,'NCE',
 34,'Supp',
 11101,'Supp') AWARD_STATUS
 FROM GRANTS G,USERS U,CONTACT_INFO CI,GRANT_BUDGET GB
 where
 g.projend > sysdate and
 g.projstart < sysdate and
 g.applications_id=gb.applications_id and
 u.user_id=g.po_user_id and
 (ci.grant_id=g.applications_id and ci.contact_type=1 )and
 gb.PERIODST between TO_DATE('01-OCT-2004') AND TO_DATE('30-SEP-2005')

View created.

Here, at the bottom of the where clause, I have hardcoded the column gb.PERIODST "TO_DATE('01-OCT-2004') AND TO_DATE('30-SEP-2005') instead of that I need to pass dynamic dates like following,
===================
instead of TO_DATE('01-OCT-2004') I need --- if month is between 9-12 then TO_DATE('01-OCT-yyyy(current year)') else yyyy-1
=======================
instead of TO_DATE('30-SEP-2005') I need --- if month is between 9-12 then I need TO_DATE('30-SEP-yyyy+1') else yyyy

I'm not sure how to achieve this on where clause.Please help me.

Thanks. 

Tom Kyte
October 27, 2004 - 8:36 pm UTC

use case -- just like in the example above in the select from dual.

Need help

A reader, October 28, 2004 - 10:27 am UTC

Hi Tom,
I'm not sure how to use case in the where clause.I tried but no luck.Could you please show me how to do using same query.I would really appreicate your help.

Tom Kyte
October 28, 2004 - 1:44 pm UTC

where (case when <whatever> then <whatever value>
when <whatever> then <whatever value>
end) = <xxxx>



just use (case ... end) wherever you could use '42' for example (anywhere you would stick a constant, an expression, a string -- you can stick (case ... end)

A reader, October 28, 2004 - 2:21 pm UTC

Hi Tom,
Please see what I'm doing wrong.


create or replace view budget_test as select g.grant_id,G.GRANTNUM,G.PROJSTART,g.projend, (select case when to_number(to_char(sysdate,'MM')) between 9 and 12
then to_char(add_months(sysdate,12),'yyyy') else
to_char(sysdate,'yyyy') end FY from dual) as FY,U.LAST_NAME,U.FIRST_NAME,CI.contact_lname,CI.contact_COMPANY,GB.DIRCOST,GB.INDCOST,GB.PERIODST,decode(gb.type,36,'Conn',39,'CptCont',
38,'Supp',
37,'Supp',
35,'Cont',
40,'New',
145,'NCE',
34,'Supp',
11101,'Supp') AWARD_STATUS
FROM GRANTS G,USERS U,CONTACT_INFO CI,GRANT_BUDGET GB
where
g.projend > sysdate and
g.projstart < sysdate and
g.applications_id=gb.applications_id and
u.user_id=g.po_user_id and
(ci.grant_id=g.applications_id and ci.contact_type=1 )and
gb.PERIODST between TO_DATE(select case when to_number(to_char(gb.PERIODST,'MM')) between 9 and 12 then to_char(add_months(sysdate,12),'yyyy') else to_char(sysdate,-12),'yyyy') end )
AND TO_DATE((select case when to_number(to_char(gb.PERIODST,'MM')) between 9 and 12 then to_char(add_months(sysdate,12),'yyyy') else to_char(sysdate,-12),'yyyy') end )

===========================

Tom Kyte
October 28, 2004 - 7:34 pm UTC

i don't see any error messages?

A reader, October 29, 2004 - 8:13 am UTC

Hi Tom,
I'm getting expression missing error.I want to know that I'm following the syntax.
Like in the where clause, I mentioned periodst date between to_date(select case...)

Thanks.


Tom Kyte
October 29, 2004 - 8:56 am UTC

don't understand all of the to'ing and fro'ing going on -- but you want:

where ...
and COLUMN between A and B

A and B are your "case" statements -- your expressions.


and column between (case when to_char(gb.PERIODST,'MM') between 9 and 12
then to_char(add_months(sysdate,12),'yyyy')
else to_char(sysdate,'yyyy')
end)
and (the other case)

A reader, October 29, 2004 - 2:26 pm UTC

Hi Tom,

Please see below.
=======================

 gb.PERIODST BETWEEN (CASE WHEN TO_CHAR(gb.PERIODST,'MM') BETWEEN 9 AND 12
THEN TO_CHAR(ADD_MONTHS(SYSDATE,12),'yyyy')
 ELSE TO_CHAR(SYSDATE,'yyyy')
END)
AND (CASE WHEN TO_CHAR(gb.PERIODST,'MM') BETWEEN 9 AND 12
THEN TO_CHAR(ADD_MONTHS(SYSDATE,12),'yyyy')
ELSE TO_CHAR(SYSDATE,'yyyy') END)
SQL> /

View created.

SQL> select count(*) from testing;
select count(*) from testing
                           *
ERROR at line 1:
ORA-01843: not a valid month 

Tom Kyte
October 29, 2004 - 5:04 pm UTC

drop the predicate from the view, does it still happen (eg: why do you believe it to be the predicate and is periodst a DATE or what type is it)

A reader, October 29, 2004 - 11:47 pm UTC

Hi Tom,
I don't understand what you mean by drop the pridicate.The column periodst is a date column.Thanks.;

Tom Kyte
October 30, 2004 - 2:10 pm UTC

predicate = where clause.

you are assuming the error comes because of the predicate, well, drop the predicate to test that theory.

all over the place ...

Gabe, November 01, 2004 - 12:55 pm UTC

To "A reader" ...

Your capacity for being clear and consistent is just terrible …

<quote> … what I'm doing wrong?</quote>

… else to_char(sysdate,-12),'yyyy') end … <== this is obviously wrong

<quote>select count(*) from testing;</quote>

If your view compiled then syntax is OK … hence the problem you have is about data <quote>ORA-01843: not a valid month</quote> … which should come to no surprise giving all the implicit type conversions you are doing all over the place: comparing strings to numbers, comparing dates to strings, converting strings to dates without a format mask.

Some people just cannot be helped … it is just a fact. In any case, if you still want some help then post a complete scenario … not just bits and pieces … you know, assume the readers of your stuff cannot read your mind and/or imagine your particular environment.

Using Greatest() to select highest Date

denni50, December 03, 2004 - 11:20 am UTC

Hi Tom

I'm modifying a procedure(not mine) and was wondering if
the Greatest Function can be used in a WHERE clause like
below:

update tablname set colA = 'Y'
where timestamp = (greatest(date_mrg,date_del))
and (id =delete_id or id=merge_id);

or does the Greatest() need to assign a value to a variable
like:

value:=greatest(date_mrg,date_del)....

reason for asking is the above update statement
is not updating colA based on the where condition.

thanks


Tom Kyte
December 03, 2004 - 1:20 pm UTC

greatest is just a function like upper, decode, whatever. it can be used like that.

thanks Tom...

denni50, December 03, 2004 - 1:32 pm UTC

did some testing to see how it works...since
I've never used it before.

(meaning Greatest Function).



less than 30 days

Otn, January 07, 2005 - 7:19 pm UTC

Create table t( date1 date, date2 date)
/

insert into t values ( sysdate, sysdate -1)
/
insert into t values ( sysdate, sysdate -40)
/


Tom

I need to query t for all those rows where the different between date1 and date2 is less than a month. Mind you some months have 30 days , some 31 and you know about FEB.

Please provide a solution.

Thanks


Tom Kyte
January 08, 2005 - 4:20 pm UTC

where months_between( date1, date2 )........

Otn

Otn, January 08, 2005 - 9:32 pm UTC

select count(*)
from t
where months_between(date1, date2) <1

I guess the above will give the answer for me.

Tom, now I want to group by month i.e. I want to display the number of rows where the months_between(date1, date2) <1, is grouped by each month of the year. The month should be taken from date1.

Will the below suffice

select to_char(date1, 'yyyymm'), count(*)
from t
where months_between(date1, date2) <1
group by to_char(date1, 'yyyymm')


Tom Kyte
January 09, 2005 - 11:25 am UTC

I'd use trunc(date1,'mm') but other than that, sure.

trunc(date1,'mm')

Otn, January 09, 2005 - 5:07 pm UTC

What will trunc(date1,'mm') ensure, why cant you use to_char ?

Tom Kyte
January 09, 2005 - 5:14 pm UTC

did I say you cannot use to_char?


I said:

I'd use trunc(date1,'mm') but other than that, sure.
^^^^^^^

Your goal is to truncate the date to the month level. Nothing does that faster than "trunc" (no nls_date routines, no conversion from date to string, etc).


That and "trunc(date1,'mm')" says explicitly "i am truncating the date to a month"

to_char( date1, 'yyyymm' ) says indirectly that, but mostly it says "we are making a string out of a date"

OK

John, January 10, 2005 - 12:26 pm UTC

Hi Tom,
What is the difference between the date formats
 dd-mon-YYYY and dd-mon-RRRR?

SQL> select to_char(current_date,'dd-mon-YYYY') as Dt    from   dual
  2  union all
  3  select to_char(current_date,'dd-mon-RRRR') as Dt from dual
  4  /

DT
-----------
10-jan-2005
10-jan-2005 

Tom Kyte
January 10, 2005 - 1:58 pm UTC

in a to_char, no different.

in a to_date, different

ops$tkyte@ORA9IR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
 
Session altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select to_date( '01-jan-05', 'dd-mon-yyyy' ),
  2         to_date( '01-jan-05', 'dd-mon-rrrr' )
  3    from dual
  4  /
 
TO_DATE('01-JAN-05', TO_DATE('01-JAN-05',
-------------------- --------------------
01-jan-0005 00:00:00 01-jan-2005 00:00:00
       ^^                   ^^ 
 

time component

dl, January 14, 2005 - 11:41 am UTC

Tom

I have read this thread with interest my problem relates to comparing time component. I would've thought this to be simple but am having difficulties:

drop table t1;
create table t1 (c1 number, c2 date);

insert into t1 values (1, sysdate - 10/4);
insert into t1 values (1, sysdate - 54/3);
insert into t1 values (1, sysdate - 53/6);
insert into t1 values (1, sysdate - 564/7);
insert into t1 values (1, sysdate - 10/2);
insert into t1 values (1, sysdate - 10/54);


select c1, c2 from t1;

C1 C2
---------- -------------------
1 12/01/2005 03:41:34
1 27/12/2004 15:41:34
1 05/01/2005 19:41:35
1 26/10/2004 01:58:45
1 09/01/2005 15:41:37
1 14/01/2005 11:14:58

Now what I want to do is look for all the rows which have dates in the morning, I tried :

select * from t1
where
c2 >= TO_DATE('00:00:00', 'HH24:MI:SS')
AND c2 <= TO_DATE('11:59:59', 'HH24:MI:SS')

but no rows.

What am i doing wrong?!

All i need to do is compare just the time component.

I also noticed that

16:35:42 OE9@DEV9>select sysdate from dual;

SYSDATE
-------------------
14/01/2005 16:35:38

1 row selected.

Elapsed: 00:00:00.00
16:37:13 OE9@DEV9>select TO_DATE('23:59:59', 'HH24:MI:SS')
16:37:14 2 from dual;

TO_DATE('23:59:59',
-------------------
01/01/2005 23:59:59

1 row selected.


when selecting just the time only from dual the date defaults to 01/01/2005 why is this?? This is the reason i think that my sql is not working.

How should i do this?

Tom Kyte
January 14, 2005 - 7:57 pm UTC

there you do have to apply a function to the date column, you cannot "between" or range on this without it.

where to_number(to_char(c2,'hh24')) between 0 and 11;

thanks but how about as a fraction

dxl, January 17, 2005 - 4:55 am UTC

1)

Thanks for that but i don't just want to compare one part of the time I need to be able to compare the whole time string down to the nearest second ie i want to be able to say

eg 11:19:57 is it bigger or smaller than another time 12:51:02


Do I have to separate each component and compare??

I am I correct in saying oracle stores the time as a fraction of a day eg 12:00:00 is 1/2 = 0.5 ??

If so how can i extract this fraction ? ie convert a full date string to its julian number including the fraction for the time?? Because then it would be easy to compare the 2 fractions.


2) also can you shed some light on why oracle defaulted the day to the 01/01/2005 :

I also noticed that

16:35:42 OE9@DEV9>select sysdate from dual;

SYSDATE
-------------------
14/01/2005 16:35:38

1 row selected.

Elapsed: 00:00:00.00
16:37:13 OE9@DEV9>select TO_DATE('23:59:59', 'HH24:MI:SS')
16:37:14 2 from dual;

TO_DATE('23:59:59',
-------------------
01/01/2005 23:59:59


seems a bit strange, why does it do that?


Many thanks

Tom Kyte
January 17, 2005 - 8:36 am UTC

use:

to_number(to_char(date,'hh24miss'))


and you'll find that 111957 is less than 125102

An Alternative?

John Spencer, January 17, 2005 - 11:06 am UTC

Tom:

Other than saving 3 characters typing, is there any benefit to:

WHERE TO_NUMBER(TO_CHAR(date,'sssss')) < 43200

over 'hh24miss'?

Thanks

Tom Kyte
January 17, 2005 - 11:16 am UTC

that works too.

to_char rules!; difference between 2 dates

Alf, February 04, 2005 - 1:05 pm UTC

Thanks Tom for this site I always find answers in here!

Hey Andrew! Thanks for method1
Exactly what I need for this:
s.logon_time "Connect Time", sysdate "Current Time",
to_char(trunc (sysdate-s.logon_time), '000') || ' days ' ||
to_char(trunc (sysdate) + mod (sysdate-s.logon_time,1),'HH24 "hrs" MI "min" SS "sec"') Session_Time_Connected

dfdfs

fssf, February 07, 2005 - 1:41 pm UTC

css
Tom Kyte
February 07, 2005 - 4:08 pm UTC

afdkl to you

DIFFERENCE PROBELM

ASIM, February 27, 2005 - 3:23 pm UTC

Hi Tom
I am facing a probelm, hope you will help..
I am using this fuction

Create Function date_diff ( p_date1 DATE, p_date2 DATE)
return char
is

 Years        NUMBER;
 months       NUMBER;
 days         NUMBER;
 day_fraction NUMBER;
 hrs          NUMBER;
 mints        NUMBER;
 sec          NUMBER;

begin

 Years :=trunc( months_between( p_date2 , p_date1 ) /12 );
 months:=mod( trunc( months_between( p_date2, p_date1 ) ), 12 );
 days  :=trunc(p_date2 -
add_months(p_date1,trunc(months_between(p_date2,p_date1) )));

 day_fraction:= (p_date2-p_date1)-trunc(p_date2-p_date1);

 hrs   :=trunc(day_fraction*24);
 mints :=trunc((((day_fraction)*24)-(hrs))*60);
 sec   :=trunc(mod((p_date2-p_date1)*86400,60));

 return(years||' Years '||months||' Months '||days||' Days '||hrs||' Hours '||mints||' Minutes & '||sec||' Seconds');

end;





and it is giving me this output

SQL> SELECT DATE_DIFF('13-MAR-1877', SYSDATE) FROM DUAL
/

DATE_DIFF('13-MAR-1877',SYSDATE)
--------------------------------------------------------------------------------
127 Years 11 Months 15 Days 1 Hours 15 Minutes & 49 Seconds


But when i do this 



SQL>SELECT DATE_DIFF('13-MAR-1877 13:07:00', SYSDATE) FROM DUAL
/
IT GIVES ME THIS ERROR...

SELECT DATE_DIFF('13-MAR-1877 13:07:00', SYSDATE) FROM DUAL
                 *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SINCE I WANT TO GET THE DIFFERENCE SINCE '15 MARCH 1877 13:07:00'

IN THE FORMAT OF 
127 Years 11 Months 15 Days ? Hours ? Minutes & ? Seconds


SO WHAT SHOULD I NEED TO DO TO GET THIS..
REGARDS,
ASIM. 

Tom Kyte
February 27, 2005 - 3:51 pm UTC

when you have a string, you need to convert the string to a date.

to_date( '13-mar-1887 13:07:00', 'dd-mon-yyyy hh24:mi:ss' )

is what you want. And likewise when retrieving a date into a string -- you want to use the appropriate format on the way out as well

Date Splits

Susan, February 28, 2005 - 4:48 am UTC

Dear Tom,

I want output to be like below. It is possible to achieve it in a single query?

StartDate EndDate Days
04-01-2004 31-01-2004 28
01-02-2004 29-02-2004 29
01-03-2004 31-03-2004 31
----
----
----
01-10-2004 17-10-2004 17

Thanks
Susan


Date Splits

Susan, February 28, 2005 - 5:11 am UTC

I was able to work out based on the examples given in this
thread. Can this be improved ?

select d1, d2, greatest(add_months(trunc(d1,'mm'),r),d1) startdate
,least(last_day(add_months(trunc(d1,'mm'),r)),d2) enddate,
(( least(last_day(add_months(trunc(d1,'mm'),r)),d2)
- greatest(add_months(trunc(d1,'mm'),r),d1) ) + 1 )days
from t1, (select rownum-1 r
from all_objects
where rownum <= ( select max(months_between(d2,d1))+1 from t1)
)
where months_between(d2,d1)+1 >= r

Thanks,
Susan

Tom Kyte
February 28, 2005 - 7:57 am UTC

not clear what the inputs are here --

is the problem "given two dates, produce a row for each month"



Date Splits

Susan, February 28, 2005 - 5:45 pm UTC

Sorry for not being clear. "Problem is given two dates,
produce a row for each month with start date, end date ,
and number of days in each month displayed".

In example given, first row should have actual start date of 04/01/2004
and last row should have actual end date of 17/10/2004.

Please suggest whether query can be improved.

StartDate EndDate Number of Days
4/01/2004 31/01/2004 28
1/02/2004 29/02/2004 29
1/03/2004 31/03/2004 31
1/04/2004 30/04/2004 30
1/05/2004 31/05/2004 31
1/06/2004 30/06/2004 30
1/07/2004 31/07/2004 31
1/08/2004 31/08/2004 31
1/09/2004 30/09/2004 30
1/10/2004 17/10/2004 17

Create table t1 (d1 date not null, d2 date not null);
INSERT INTO T1
(d1, d2)
values
(
TO_DATE('04-JAN-2004','DD-MON-YYYY'),
TO_DATE('17-OCT-2004','DD-MON-YYYY')
)

select d1, d2, greatest(add_months(trunc(d1,'mm'),r),d1) startdate
,least(last_day(add_months(trunc(d1,'mm'),r)),d2) enddate,
(( least(last_day(add_months(trunc(d1,'mm'),r)),d2)
- greatest(add_months(trunc(d1,'mm'),r),d1) ) + 1 )days
from t1, (select rownum-1 r
from all_objects
where rownum <= ( select max(months_between(d2,d1))+1 from t1)
)
where months_between(d2,d1)+1 >= r

Thanks,
Susan.

Tom Kyte
March 01, 2005 - 8:59 am UTC

that looks fine actually -- same approach i would take.

Generate the set of rownums (numbers 1, 2, ... N) to cover the months and then fetch out the right start/stop for each month.

OK

Siva, March 02, 2005 - 10:28 am UTC

i Tom,
Is it possible to format the date that we enter and store it in
the table? Please see below.

SQL> create table t(
  2  x date default to_char(x,'mm/dd/yyyy')
  3  )
  4  /
x date default to_char(x,'mm/dd/yyyy')
                       *
ERROR at line 2:
ORA-00984: column not allowed here


Any workaround for this?
Please do reply.
 

Tom Kyte
March 02, 2005 - 11:01 am UTC

to_char converts a date to a string.
to_date converts a string to a date.


a format is applied to a DATE variable upon retrieval to present it to you in some appealing format.

It is not used to "store" the data.


create table t ( x date );

select to_char(x,'mm/dd/yyyy') x from t;


is what you are looking for. dates are simply a 7 byte field that stores

YY century
YY year
MM month
DD day
HH24 hour
MI minutes
SS seconds.

if you are trying to make sure that the date as inserted does not have a time component,


...
x date check (x = trunc(x,'dd')),
...


would verify programs are inserting the right stuff, or

create trigger....
begin
:new.x := trunc(:new.x,'dd');
end;
/


would silently remove any time component

but you would STILL use to_char on the way out to format it pretty.

Getting sysdate from another machine

Aditya Saraogi, June 21, 2005 - 5:57 am UTC

Hi Tom,
I want to retrieve the sysdate from another machine (say B).
Something like:

select sysdate from dual@db_link_name;

This obviously does not work. How can I then retrieve the sysdate from another database (while being connected to another DB), the condition being that I cannot create any object on the target machine.

Thanks

Tom Kyte
June 21, 2005 - 4:43 pm UTC

use dbms_sql to run a query on the remote site.

dbms_sql@remote_site.parse( 'begin :x := sysdate; end;' )

use dbms_sql@remote_site to bind and execute and open and all.

(I find it easiest to set up a private synonym for dbms_sql@remote_site)

DB Link error

Aditya Saraogi, June 22, 2005 - 7:37 am UTC

Hi Tom,
I had tried to implement your idea. However I am not very conversant with the DBMS_SQL package and I am not sure how to use it, to obtain the required output. 

The DBLink was created as

CREATE DATABASE LINK testravi
CONNECT TO SCOTT IDENTIFIED BY tiger
USING 'ravindra';

I wrote the following anonymous block

declare
var_date    DATE;
begin
var_date := sysdate;
dbms_sql@testravi.parse('begin :var_date := sysdate; end;');
dbms_output.put_line(to_char(var_date,'DD MM YYYY HH24:MI:SS'));
end;

The error thrown out was as following:

SQL> show user;
USER is "DARTTEST"
SQL> declare
  2  var_date DATE;
  3  begin
  4  var_date := sysdate;
  5  dbms_sql@testravi.parse('begin :var_date := sysdate; end;');
  6  dbms_output.put_line(to_char(var_date,'DD MM YYYY HH24:MI:SS'));
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-04054: database link TESTRAVI.PARSE does not exist

Please advise.
Thanks
Aditya Saraogi 

Tom Kyte
June 22, 2005 - 1:53 pm UTC

search for dbms_sql on this site, tons of examples. There is documentation on the supplied packages as well with examples.

You'll want to use the synonym I described to make life "easier"



Creating Synonyms in the Procedure

Aditya Saraogi, July 06, 2005 - 2:32 am UTC

Hi Tom,
I was able to implement the dbms_sql@i_dblink concept by using a synonym. Thanks!

My requirement necessiates creating multiple db links and get the system time after connecting to the databases. How ever I am not able to dynamically create a synonym for dbms_sql@dblink in the stored procedure.
The error I get is
ORA-01031: insufficient privileges

I do have the necessary privileges as it is possible for me to create the synonym from the regular SQL Plus prompt.

The procedure written by me is as following:

CREATE OR REPLACE PROCEDURE getSysdate
(
i_dblink IN dblinks.dblink%type
)

IS
l_var_date DATE;
l_cursor_name NUMBER;
l_rows_processed NUMBER;
l_str VARCHAR2(200);
BEGIN
l_str := 'CREATE SYNONYM db_s for dbms_sql@'||i_dblink;
execute immediate l_str;
END getSysdate;

Thanks,
Aditya Saraogi

Tom Kyte
July 06, 2005 - 7:47 am UTC

What am i doing wrong?

Aditya Saraogi, July 06, 2005 - 7:36 am UTC

Hi Tom,
I was able to rectify the previous problem by using the AUTHID Current_User construct, but I am not able to get why should a privilege conflict arise when both the onwer and user of the procedure are same?

I had developed on the previous procedure to get the following sp:

CREATE OR REPLACE PROCEDURE getSysdate
(
i_dblink IN dblinks.dblink%type
,o_result OUT DATE
)
AUTHID CURRENT_USER
IS
l_var_date DATE;
l_cursor_name NUMBER;
l_rows_processed NUMBER;
l_str VARCHAR2(200);
BEGIN


l_str := 'CREATE OR REPLACE SYNONYM l_synonym for sys.dbms_sql@'||i_dblink;
execute immediate l_str;

l_cursor_name := l_synonym.open_cursor;

l_synonym.parse(l_cursor_name,'SELECT SYSDATE FROM DUAL',dbms_sql.NATIVE);
l_synonym.DEFINE_COLUMN(l_cursor_name,1,l_var_date);

l_rows_processed := l_synonym.EXECUTE(l_cursor_name);
loop
exit when ( l_synonym.fetch_rows(l_cursor_name) <= 0);
l_synonym.COLUMN_VALUE(l_cursor_name,1,l_var_date);
end loop;

o_result := l_var_date;
l_synonym.CLOSE_CURSOR(l_cursor_name);

END getSysdate;
/

As you can see, I have to first explicitly define the synonym, l_synonym, and then only can I successfully compile the procedure.
After doing that when I try to execute the stored procedure with different values for i_dblink, the procedure always returs the time from the database whose dblink I had initially used to create the synonym(prior to the compilation of the procedure).
I checked the definition of the synonym after every test run of the procedure, and the definition of the synonym changed in accordance to the db_link provided by me, but still the time being returned was sourced from the same database.
Could you please tell me where am I erring ?

Thanks
Aditya Saraogi

Tom Kyte
July 06, 2005 - 8:23 am UTC

chicken + egg, which comes first.

you create the synonym, the procedure is going to go invalid.  but you are running said procedure.  


ops$tkyte-ORA10G> create or replace function get_remote_date( p_syn in varchar2 ) return date
  2  as
  3          l_the_date date;
  4  begin
  5
  6  execute immediate replace( '
  7  declare
  8          l_cursor integer;
  9          l_date   date;
 10  begin
 11          l_cursor := DBMS_SQL.open_cursor@$SYN$;
 12
 13          dbms_sql.parse@$SYN$
 14          ( l_cursor,
 15           ''select sysdate d from dual'',
 16            dbms_sql.native );
 17
 18          dbms_sql.define_column@$SYN$
 19          ( l_cursor, 1, l_date );
 20
 21          if ( dbms_sql.execute_and_fetch@$SYN$
 22               ( l_cursor, TRUE ) != 1
 23             )
 24          then
 25                  raise_application_error( -20001, ''failed'' );
 26          end if;
 27
 28          dbms_sql.column_value@$SYN$( l_cursor, 1, l_date );
 29
 30          :x := l_date;
 31  end;
 32  ', '$SYN$', p_syn ) using IN OUT l_the_date;
 33
 34  return l_the_date;
 35
 36  end;
 37  /
 
Function created.
 
ops$tkyte-ORA10G>
ops$tkyte-ORA10G> exec dbms_output.put_line( get_remote_date( 'ora9ir2' ) );
06-JUL-05
 
PL/SQL procedure successfully completed.
 

AUTHID and Autocompile

Aditya Saraogi, July 07, 2005 - 12:50 am UTC

Hi Tom,
Really cool solution. Goes to show the essenital ingredient of clear thinking in solving a problem. I was focussing on creating a synonym rather than getting the system date!

I have two doubts
1. The synonym in my procedure was being recreated during the execution of the procedure. Then why was it that even in the next run, it seemed as if the initial definition was being used to return the date.
2. Why was I not allowed to create a synonym in my own procedure, without first using the AUTHID CURRENT_USER construct?
For instance as a user say SCOTT, I am able to create the synonym via SQL PLUS. On the same schema I create a procedure which runs an execute immediate command carrying the instructions for creating a synonym. I run the procedure as SCOTT and the error is thrown. Why is AUTHID CURRENT_USER required?

Thank You
Aditya Saraogi

Tom Kyte
July 07, 2005 - 9:11 am UTC

1) not sure what you mean, not sure it ever worked right.
2) did you read provided link above?

</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

Thanks

Aditya Saraogi, July 09, 2005 - 1:57 am UTC

Hi Tom,
Thanks for the link . I had missed the link altogether in your previous reply. Sorry about that. Have recently purchased your book, Expert 1 on 1. Its reading is going to be like the dessert after a meal!

Thanks
Aditya Saraogi

30 days per month

A reader, August 08, 2005 - 9:21 am UTC

Our accounting rule is that all months have 30 days.

Given this rule, how can I calculate the number of days between 2 dates?

For example

ALTER SESSION SET nls_date_format='mm/dd/yyyy'

SELECT TO_DATE('4/30/2005')-TO_DATE('7/12/2004')+1 FROM dual

gives me 293

but if I apply the rule above (30 days per month), I get 288.

How can I modify my query to get 288?

Thanks


Tom Kyte
August 08, 2005 - 7:48 pm UTC

I have no idea how to deal with that accounting rule.  

but if you use months_between....

I don't get 288.  

ops$tkyte@ORA9IR2> SELECT months_between( TO_DATE('4/30/2005','mm/dd/yyyy'),TO_DATE('7/12/2004','mm/dd/yyyy')+1) FROM dual;

MONTHS_BETWEEN(TO_DATE('4/30/2005','MM/DD/YYYY'),TO_DATE('7/12/2004','MM/DD/YYY
-------------------------------------------------------------------------------
                                                                      9.5483871

ops$tkyte@ORA9IR2> SELECT 30*months_between( TO_DATE('4/30/2005','mm/dd/yyyy'),TO_DATE('7/12/2004','mm/dd/yyyy')+1) FROM dual;

30*MONTHS_BETWEEN(TO_DATE('4/30/2005','MM/DD/YYYY'),TO_DATE('7/12/2004','MM/DD/
-------------------------------------------------------------------------------
                                                                     286.451613


so I really don't know the logic you used (and once you write it down, the function probably falls right out on the paper :) 

A reader, August 08, 2005 - 9:22 pm UTC

288 as in

SELECT 18 + 30*months_between(
trunc(TO_DATE('4/30/2005','mm/dd/yyyy'),'mm'),
trunc(TO_DATE('7/12/2004','mm/dd/yyyy'),'mm')) FROM
dual;

where 18 is 30-12 (the 18 days left in July 2004)

Something like that?

Tom Kyte
August 09, 2005 - 9:15 am UTC


but what happens if it was 4/12/2005 and 7/30/2004

I'd like the orginal poster to define the algorithm to be used (and once they do that, creating the function will be "simple")

difference between 2 dates

Geo, August 10, 2005 - 9:24 am UTC

DECLARE
end_date DATE := '31-MAR-1996';
start_date DATE := '28-FEB-1994';
start_date_lastday DATE;
end_date_firstday DATE;
months_num PLS_INTEGER;
years_num PLS_INTEGER;
days_num PLS_INTEGER;
BEGIN
end_date_firstday := LAST_DAY (ADD_MONTHS (end_date, -1));
start_date_lastday := LAST_DAY (start_date);
months_num := MONTHS_BETWEEN (end_date_firstday, start_date_lastday);
IF end_date = last_day(end_date) THEN
months_num := months_num + 1;
end_date_firstday := end_date;
END IF;
years_num := TRUNC (months_num) / 12;
months_num := months_num - (years_num * 12);
days_num := (start_date_lastday - start_date) + (end_date - end_date_firstday);
DBMS_OUTPUT.put_line (years_num || ' years, ' || months_num || ' months, ' || days_num || ' days');
END;


Tom Kyte
August 10, 2005 - 10:34 am UTC

<quote from a book being released soon, if the link 
http://asktom.oracle.com/Misc/DateDiff.html
didn't do it for you, this is the 9i and above easier way>


Getting the Difference Between Two DATEs

Another frequently asked question is, “How do I retrieve the difference between two dates?” The answer is deceptively simple: you just subtract them. This will return a number representing the number of days between the two dates. 

Additionally, you have the built-in function MONTHS_BETWEEN that will return a number representing the number of months—including fractional months—between two dates. Lastly, with the INTERVAL types, you have yet another method to see the elapsed time between two dates. The following SQL query demonstrates the outcome of subtracting two dates (showing the number of days between them), using the MONTHS_BETWEEN function and then the two functions used with INTERVAL types:

ops$tkyte@ORA10G> select dt2-dt1 ,
  2         months_between(dt2,dt1) months_btwn,
  3         numtodsinterval(dt2-dt1,'day') days,
  4         numtoyminterval(months_between(dt2,dt1),'month') months
  5    from (select to_date('29-feb-2000 01:02:03','dd-mon-yyyy hh24:mi:ss') dt1,
  6                 to_date('15-mar-2001 11:22:33','dd-mon-yyyy hh24:mi:ss') dt2
  7            from dual )
  8  /
 
   DT2-DT1 MONTHS_BTWN DAYS                           MONTHS
---------- ----------- ------------------------------ -------------
380.430903  12.5622872 +000000380 10:20:30.000000000  +000000001-00

Those are all “correct” values, but not of great use to us yet. Most applications would like to display the years, months, days, hours, minutes, and seconds between the dates. Using a combination of the preceding functions, we can achieve that goal. We’ll select out two intervals: one for the years and months, and the other for just the day, hours, and so on. We’ll use the MONTHS_BETWEEN built-in function to determine the decimal number of months between the two dates, and then we’ll use the NUMTOYMINTERVAL built-in function to convert that number into the years and months. Additionally, we’ll use MONTHS_BETWEEN to subtract the integer number of months between the two dates from the larger of the two dates to get down to the days and hours between them:

ops$tkyte@ORA10G> select numtoyminterval
  2         (months_between(dt2,dt1),'month')
  3             years_months,
  4         numtodsinterval
  5             (dt2-add_months( dt1, trunc(months_between(dt2,dt1)) ),
  6              'day' )
  7             days_hours
  8    from (select to_date('29-feb-2000 01:02:03','dd-mon-yyyy hh24:mi:ss') dt1,
  9                 to_date('15-mar-2001 11:22:33','dd-mon-yyyy hh24:mi:ss') dt2
 10            from dual )
 11  /
 
YEARS_MONTHS    DAYS_HOURS
--------------- ------------------------------
+000000001-00   +000000015 10:20:30.000000000

Now it is clear that there is 1 year, 15 days, 10 hours, 20 minutes, and 30 seconds between the two DATEs.
 

Beautiful! Thats very helpful.

A reader, August 10, 2005 - 11:18 am UTC


Difference of 30 mintues does not return 30 but 29

Juan Velez, August 29, 2005 - 9:24 pm UTC

select trunc(mod((to_date('200508291930','yyyymmddhh24mi')-
to_date('200508291900','yyyymmddhh24mi')
)* 24*60,60))
from dual

returns 29 not 30.

The same applying simple date arithmetic

select (
to_date('200508291930','yyyymmddhh24mi')-
to_date('200508291900','yyyymmddhh24mi')
) * 1440
from dual

returns 29.9999999999999999999999999995 not 30

Why? How can this be avoided? It also happens for 15, 5, 10, etc differences. It never returns the integer value. And sometimes to get the integer value you may need to use round or trunc. Not really deterministic.

What am I missing?

Thank you,

Juan A.

Tom Kyte
August 30, 2005 - 1:46 am UTC

adding 0.000001 is an option to correct for this - the repeating number is a problem (it repeats 33333333333333333 forever)

Difference of 30 mintues does not return 30 but 29

Juan Velez, September 08, 2005 - 6:21 pm UTC

My question was more towards asking why Oracle does not return an integer value. The problem is not only for 30 minutes but many other ranges. I imagine the only way to fix this, is by creating a function that returns the correct value(I think it only works in 9i and 10g as it requires timestamp). How could it have been done prior to timestamp?

FUNCTION GetNumberOfMinutes(
pDate1 IN DATE,
pDate2 IN DATE)
RETURN NUMBER DETERMINISTIC IS

x TIMESTAMP := CAST(pDate1 AS TIMESTAMP);
y TIMESTAMP := CAST(pDate2 AS TIMESTAMP);

BEGIN
RETURN (EXTRACT(DAY FROM (X-Y) )*24*60*60+
EXTRACT(HOUR FROM (X-Y) )*60*60+
EXTRACT(MINUTE FROM (X-Y) )*60+
EXTRACT(SECOND FROM (X-Y) ))/60;
END;


Tom Kyte
September 08, 2005 - 6:33 pm UTC

you are dealing with numbers that basically have 5 things after the decimal place, rounding happens.

Could you expand your explanation?

Juan Velez, September 08, 2005 - 9:38 pm UTC

Could you expand "you are dealing with numbers that basically have 5 things after the decimal
place, rounding happens."?

I would appreciate and like to learn why this happens. I thought Oracle treated dates differently when doing arithmetic between them.

I already fixed my problem but wanted to know why this happens.

Thank you,

JA

Tom Kyte
September 09, 2005 - 6:39 am UTC

sorry -- shouldn't have used 5, but basicallly we have a repeating number that would require a datatype with infinite precision to represent:

ops$tkyte@ORA10G> set numformat 9.999999999999999999999999999999999999
ops$tkyte@ORA10G> select (1/24/60/60)*30 from dual;
 
                        (1/24/60/60)*30
---------------------------------------
  .000347222222222222222222222222222222
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select to_date('01-jan-2005 00:00:30','dd-mon-yyyy hh24:mi:ss')
  2        -to_date('01-jan-2005 00:00:00','dd-mon-yyyy hh24:mi:ss')
  3    from dual;
 
TO_DATE('01-JAN-200500:00:30','DD-MON-YYYYHH24:MI:SS')-TO_DATE('01-JAN-200500:0
-------------------------------------------------------------------------------
                                          .000347222222222222222222222222222222


we don't have infinite precision, we have 38 digits, hence when we multiply back by 1440:


ops$tkyte@ORA10G> select (1/24/60/60)*(24*60) from dual;
 
                   (1/24/60/60)*(24*60)
---------------------------------------
  .016666666666666666666666666666666667


we got yet another "infinitely long" number.

rounding occurs. 

Difference in minutes betwee 2 dates

Juan Velez, September 13, 2005 - 9:45 pm UTC

Thanks for your explanation. Now I am wondering how would you have created a function in 8i and before that returned the difference in minutes between 2 dates?

JA

Tom Kyte
September 14, 2005 - 7:53 am UTC

fudge factor, just add 0.000001 to fudge it up.

<quote>boundary values, gotta hate them...</quote>

Duke Ganote, October 10, 2005 - 5:31 pm UTC

I just got nicked by ADD_MONTHS when the date is the last day of the February before a leap year:

with X as
( select TO_DATE('2003-FEB-28','YYYY-MON-DD') AS Feb_28_2003
from dual )
select TO_CHAR( Feb_28_2003,
'YYYY-MON-DD') AS "2003-FEB-28"
, TO_CHAR( add_months( Feb_28_2003, +12),
'YYYY-MON-DD') AS "2004-FEB-??"
from X
/
2003-FEB-28 2004-FEB-??
----------- -----------
2003-FEB-28 2004-FEB-29

ADD_MONTHS implicitly assumes you want the end of month.

Tom Kyte
October 10, 2005 - 7:36 pm UTC

add_months is DOCUMENTED to return the last day of the month in question when you add a month to the last day of a month.


Happens for all the months - add one month to sept 30th.

Read the (OTN) Manual

Duke Ganote, October 10, 2005 - 9:11 pm UTC

Sure enough! The documentation scattered around the web may not be very clear, but Oracle says "If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month."
</code> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/functions004.htm <code>

Gary

A reader, October 14, 2005 - 7:43 am UTC

Tom
I have a date table that has data like

date_pk
20050101
20050102
20050103
..
..

The date_pk is unique in the table..

I need a sql that can give the follwing output (range of 5)

20050101 20050105
20050106 20050110
20050111 20050115
..
...

Is it possible?
Thanks


Tom Kyte
October 14, 2005 - 9:01 am UTC

ops$tkyte@ORA10G> create table t
  2  as
  3  select to_number( to_char( to_date('01012005','ddmmyyyy')+rownum-1, 'yyyymmdd' ) ) x
  4    from all_users;
 
Table created.


didn't know if you really mean "5" or "5 within a month"...

so, I'll give you "5 within a month" first.

ops$tkyte@ORA10G> select yyyy, mm, min(dd), max(dd)
  2    from (
  3  select trunc(x/10000) yyyy, mod( trunc(x/100), 100 ) mm, mod(x,100) dd
  4    from t
  5         )
  6   group by yyyy, mm, trunc( (dd-0.01)/5 )
  7  /
 
      YYYY         MM    MIN(DD)    MAX(DD)
---------- ---------- ---------- ----------
      2005          1          1          5
      2005          1          6         10
      2005          1         11         15
      2005          1         16         20
      2005          1         21         25
      2005          1         26         30
      2005          1         31         31
      2005          2          1          2
 
8 rows selected.


Or, if you just wanted "up to 5 rows grouped together after sorting"

ops$tkyte@ORA10G> select min(x), max(x)
  2    from (
  3  select x, rownum r
  4    from (
  5  select x
  6    from t
  7   order by x
  8         )
  9         )
 10   group by trunc((r-0.01)/5);
 
    MIN(X)     MAX(X)
---------- ----------
  20050101   20050105
  20050106   20050110
  20050111   20050115
  20050116   20050120
  20050121   20050125
  20050126   20050130
  20050131   20050202
 
7 rows selected.
 

Gary

A reader, October 14, 2005 - 9:24 am UTC

This is absolutely amazing...The trunc((r-0.01)/5) trick does the job real nice...Man..you are good

weeks

Tom, November 01, 2005 - 9:21 am UTC

Is it possible to achive the following thing..
we store weeks in our date dimension tables as 200301,200302...200352...
I am trying to find the difference between 2 weeks
like 200304 - 200301..here its 3 (direct difference)..but what incase its 200401 - 200351....??
any ideas
thanks

Tom Kyte
November 01, 2005 - 11:12 am UTC

my idea 


is ALWAYS TO USE A DATE FOR A DATE
and a number to store - a number..............

and nothing else.

looks like you have dates here.  


what function using to_date/substr/whatever would you use to convert your number into an Oracle date - supply that, and then we can subtract and divide by 7.


(eg: how many weeks does a year have - unfortunately, it is not constant typically, but it really utterly depends on your definition of when the weeks begin and end - I've no idea what date 200301 is...



  1  select to_char(to_date('31-dec-'||r),'yyyyiw'),
  2  to_char(to_date('01-jan-'||(r+1)),'yyyyiw')
  3* from (select level r from dual connect by level <= 10 )
ops$tkyte@ORA10GR2> /

TO_CHA TO_CHA
------ ------
200101 200201
200201 200301
200301 200401
200453 200553
200552 200652
200652 200701
200701 200801
200801 200901
200953 201053
201052 201152

10 rows selected.


Now, if a week is just every seven days starting from 01-jan, you can always just take


ops$tkyte@ORA10GR2> select trunc(to_date( '2005', 'yyyy' ),'y')+5*7 from dual;

TRUNC(TO_
---------
05-FEB-05


your yyyy portion, trunc it, add you DD*7 portion - and do that to both "dates" and subtract.
 

A reader, November 01, 2005 - 12:43 pm UTC

Thanks for the update
The problem is we follow a fiscal calendar
say year is 2005
We have 52 weeks(200501..200552),12 months (200501--200512) ,2 seasons(20051,20052) , 4quarters(20051,20052,20053,20054) .i have pasted a sample data for the first quarter

what i need is to find the number of weeks between 200510 and 200504 ..like 6..WK--week,FY -fiscal,st -start,ed -end
FY_DATE WK_ST_DATE WK_ED_DATE FY_WK FY_MONTH
20050130 20050130 20050205 200501 200501
20050131 20050130 20050205 200501 200501
20050201 20050130 20050205 200501 200501
20050202 20050130 20050205 200501 200501
20050203 20050130 20050205 200501 200501
20050204 20050130 20050205 200501 200501
20050205 20050130 20050205 200501 200501
20050206 20050206 20050212 200502 200501
20050207 20050206 20050212 200502 200501
20050208 20050206 20050212 200502 200501
20050209 20050206 20050212 200502 200501
20050210 20050206 20050212 200502 200501
20050211 20050206 20050212 200502 200501
20050212 20050206 20050212 200502 200501
20050213 20050213 20050219 200503 200501
20050214 20050213 20050219 200503 200501
20050215 20050213 20050219 200503 200501
20050216 20050213 20050219 200503 200501
20050217 20050213 20050219 200503 200501
20050218 20050213 20050219 200503 200501
20050219 20050213 20050219 200503 200501
20050220 20050220 20050226 200504 200501
20050221 20050220 20050226 200504 200501
20050222 20050220 20050226 200504 200501
20050223 20050220 20050226 200504 200501
20050224 20050220 20050226 200504 200501
20050225 20050220 20050226 200504 200501
20050226 20050220 20050226 200504 200501
20050227 20050227 20050305 200505 200502
20050228 20050227 20050305 200505 200502
20050301 20050227 20050305 200505 200502
20050302 20050227 20050305 200505 200502
20050303 20050227 20050305 200505 200502
20050304 20050227 20050305 200505 200502
20050305 20050227 20050305 200505 200502
20050306 20050306 20050312 200506 200502
20050307 20050306 20050312 200506 200502
20050308 20050306 20050312 200506 200502
20050309 20050306 20050312 200506 200502
20050310 20050306 20050312 200506 200502
20050311 20050306 20050312 200506 200502
20050312 20050306 20050312 200506 200502
20050313 20050313 20050319 200507 200502
20050314 20050313 20050319 200507 200502
20050315 20050313 20050319 200507 200502
20050316 20050313 20050319 200507 200502
20050317 20050313 20050319 200507 200502
20050318 20050313 20050319 200507 200502
20050319 20050313 20050319 200507 200502
20050320 20050320 20050326 200508 200502
20050321 20050320 20050326 200508 200502
20050322 20050320 20050326 200508 200502
20050323 20050320 20050326 200508 200502
20050324 20050320 20050326 200508 200502
20050325 20050320 20050326 200508 200502
20050326 20050320 20050326 200508 200502
20050327 20050327 20050402 200509 200502
20050328 20050327 20050402 200509 200502
20050329 20050327 20050402 200509 200502
20050330 20050327 20050402 200509 200502
20050331 20050327 20050402 200509 200502
20050401 20050327 20050402 200509 200502
20050402 20050327 20050402 200509 200502
20050403 20050403 20050409 200510 200503
20050404 20050403 20050409 200510 200503
20050405 20050403 20050409 200510 200503
20050406 20050403 20050409 200510 200503
20050407 20050403 20050409 200510 200503
20050408 20050403 20050409 200510 200503
20050409 20050403 20050409 200510 200503
20050410 20050410 20050416 200511 200503
20050411 20050410 20050416 200511 200503
20050412 20050410 20050416 200511 200503
20050413 20050410 20050416 200511 200503
20050414 20050410 20050416 200511 200503
20050415 20050410 20050416 200511 200503
20050416 20050410 20050416 200511 200503
20050417 20050417 20050423 200512 200503
20050418 20050417 20050423 200512 200503
20050419 20050417 20050423 200512 200503
20050420 20050417 20050423 200512 200503
20050421 20050417 20050423 200512 200503
20050422 20050417 20050423 200512 200503
20050423 20050417 20050423 200512 200503
20050424 20050424 20050430 200513 200503
20050425 20050424 20050430 200513 200503
20050426 20050424 20050430 200513 200503
20050427 20050424 20050430 200513 200503
20050428 20050424 20050430 200513 200503
20050429 20050424 20050430 200513 200503
20050430 20050424 20050430 200513 200503


Thanks
Tom


Tom Kyte
November 02, 2005 - 4:50 am UTC

give me the function that converts your YYYYWW into a date.

actually, once you do that - you can do it yourself :)




A reader, November 03, 2005 - 1:57 pm UTC

That was funny :)

I know i cant do that..but i was thinking something like joining the week back to the table to grab the min or max of the week start date and then using a function ...

either ways,
Is there a weeks_between function in oracle?

Tom Kyte
November 04, 2005 - 2:47 am UTC

weeks_between := trunc( (dt1-dt2)/7 )

Problem with using add_months and Months_Between

Anandkumar, November 10, 2005 - 6:28 am UTC

Dear Tom ,

Try these two SELECTs statements !

select
months_between('28.03.2003','28.02.2003') Mnth_Bet1 ,
months_between('29.03.2003','28.02.2003') Mnth_Bet2 ,
months_between('30.03.2003','28.02.2003') Mnth_Bet3 ,
months_between('31.03.2003','28.02.2003') Mnth_Bet4
from dual
/


May be we should not use month_betweens to calculate days .

Using add_months could be have the potentual problem too .

select
rownum ,
Start_Date ,
add_months(Start_Date,rownum) Add_Start_Date ,
add_months(Start_Date,rownum) - Start_Date Diff_Start_Date ,
Mid_Date ,
add_months(Mid_Date,rownum) Add_Mid_Date ,
add_months(Mid_Date,rownum) - Mid_Date Diff_Mid_date ,
End_Date ,
add_months(End_Date,rownum) End_Date ,
add_months(End_Date,rownum) - End_Date Diff_End_Date
from ( select
rownum , to_date('28.02.2003') Start_Date , to_date('29.09.2003') Mid_Date , to_date('30.09.2003') End_Date
from dual connect by level < 13)
/

Tom , Correct me if I'am wrong !



Tom Kyte
November 11, 2005 - 11:44 am UTC

you cannot use months to compute days.

Think about it - answer this:


how many days are in a month?


You cannot answer that.


If you want to get months between use months_between. If you want days between, just subtract.

where is problem ?

parag jayant patankar, May 10, 2006 - 9:01 am UTC

Hi Tom,

I have used following your query to find out duration

  1  select start_date, end_date, months_between( end_date, start_date ) x1,
  2         trunc( months_between( end_date, start_date ) /12 ) Years,
  3         mod( trunc( months_between( end_date, start_date )), 12 ) months,
  4         greatest(0, (end_date - add_months(start_date,trunc( months_between(end_date, start_date))))
  5  from ( select to_date('20060531', 'RRRRMMDD') end_date,
  6                to_date('20060228', 'RRRRMMDD') start_date
  7          from   dual
  8*      )
18:41:50 SQL> /

START_DAT END_DATE          X1      YEARS     MONTHS       DAYS
--------- --------- ---------- ---------- ---------- ----------
28-FEB-06 31-MAY-06          3          0          3          0

According to me it should be 3 months 3 days. Kindly suggest where I had gone wrong.

thanks & regards
pjp 

Tom Kyte
May 10, 2006 - 9:27 am UTC

why, they are both the last day of the month.

Months are not of fixed size. It makes no sense to say "3 days" in this particular case - last day of the month is a special case by our convention (our being "people").



compare only time component

karma, January 30, 2007 - 11:17 am UTC

I have one column defined timestamp in table A and date as another columns in table B. What is the best way to join this table efficiently to compare only time component?

Also how should we index on both tables


Tom Kyte
January 31, 2007 - 10:37 am UTC

join does NOT imply index. If you are joining every row in a table to every row in another table - you would want two full scans and a hash join typically (unless initial response time was your goal)


you would use a function to extract just the time component and compare using equality.

Weird DateDiff Error

An Avid Reader, May 23, 2007 - 3:52 pm UTC

I'm having problems using the function supplied in this thread within a PL/SQL package and it seems to be isolated when the end time is set to midnight. It works great from the SQL prompt and when called directly from the package:

SQL> SELECT (to_date('05-MAR-07 12.00.00 AM', 'DD-MON-YY HH.MI.SS AM')
  2    - to_date('04-MAR-07 06.00.00 PM', 'DD-MON-YY HH.MI.SS AM'))
  3    * DECODE( UPPER('mi'), 'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL ) diff
  4  FROM DUAL;

      DIFF
----------
       360

SQL> select pkg_metrics.datediff('MI',
  2    to_date('04-MAR-07 06.00.00 PM', 'DD-MON-YY HH.MI.SS AM'),
  3    to_date('05-MAR-07 12.00.00 AM', 'DD-MON-YY HH.MI.SS AM') ) diff from dual;

      DIFF
----------
       360


But when I used a stored procedure to call it I get a ORA-06502: PL/SQL: numeric or value error: number precision value too large error. That would normally indicate a problem with variable size but I'm pretty sure I've checked that:

..snip
    l_diff NUMBER(9) := 0;
    CURSOR date_cur(i_date in DATE) IS
        select to_char(start_time, 'HH24') + 0 st,
        decode(to_char(end_time, 'HH24') - 1, -1, 24, to_char(end_time, 'HH24') - 1) et,
        start_time, end_time
        from core_hours where app_id=i_app and core_date = i_date;
    date_cur_rec date_cur%ROWTYPE;

    CURSOR day_cur(i_date in DATE) IS
        select to_char(start_time, 'HH24') + 0 st,
        decode(to_char(end_time, 'HH24') - 1, -1, 24, to_char(end_time, 'HH24') - 1) et,
        start_time, end_time
        from core_hours where app_id=i_app and core_day = rtrim(to_char(i_date, 'Day'));
    day_cur_rec day_cur%ROWTYPE;
BEGIN
    select (to_char(last_day(l_date), 'dd') + 0) into l_num_days from dual;

    WHILE l_day <= l_num_days
    LOOP
        OPEN date_cur(l_date);
        FETCH date_cur INTO date_cur_rec;
        IF date_cur%NOTFOUND then
            CLOSE date_cur;
            OPEN day_cur(l_date);
            FETCH day_cur INTO day_cur_rec;
            IF day_cur%NOTFOUND or day_cur_rec.st is null then
                l_noncore := l_noncore + 1440;
            ELSE
                -- HERE'S THE PROBLEM LINE                
                l_diff := datediff('MI', day_cur_rec.start_time, day_cur_rec.end_time);
                l_core := l_core + l_diff;
                l_noncore := l_noncore + (1440 - l_diff);
            END IF;
            CLOSE day_cur;
        ELSE
            l_diff := datediff('MI', date_cur_rec.start_time, date_cur_rec.end_time);
            l_core := l_core + l_diff;
            l_noncore := l_noncore + (1440 - l_diff);
            CLOSE date_cur;
        END IF;
        l_date := l_date + 1;
        l_day := l_day + 1;
    END LOOP;       
..snip


The code works until the loop hits the dates from the 2 working examples above.

TIA
Tom Kyte
May 26, 2007 - 9:39 am UTC

give us a test case we can actually run in our database - no snip.... full code BUT SHORT CODE -get rid of anything not relevant to the problem.

Weird DateDiff Error pt 2

An Avid Reader, May 23, 2007 - 6:01 pm UTC

It occurred to me I didn't include the actual function:

FUNCTION datediff( i_what IN VARCHAR2, i_start IN DATE, i_end IN DATE ) RETURN NUMBER
as
    l_result    NUMBER(9);
BEGIN
    SELECT (i_end-i_start) *
           DECODE( UPPER(i_what),
                   'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL )
     INTO l_result FROM DUAL;

    RETURN l_result;
EXCEPTION    
   WHEN OTHERS THEN
      g_where := '-> ERROR from function datediff('|| SQLCODE || ',' || SUBSTR(SQLERRM,1,100) || ')';
   RAISE;
END datediff;

Tom Kyte
May 26, 2007 - 9:40 am UTC

now, show us how to call this to produce your issue - small test case, complete with code to reproduce your problem.

A Weird DateDiff Error, cont.

An Avid Reader, May 25, 2007 - 1:22 pm UTC

Sorry to make a short story long but there must be an issue with the database in the way the dates are stored. The errors above only occur when I use the values stored in my table:

SQL> select to_char(start_time,'DD-MON-YY HH.MI.SS AM') strt,
  2    to_char(end_time, 'DD-MON-YY HH.MI.SS AM') endt,
  3    end_time - start_time
  4  from core_hours where core_date = '04-mar-07' and app_id=69;

STRT                  ENDT                  END_TIME-START_TIME
--------------------- --------------------- -------------------
04-MAR-07 06.00.00 PM 05-MAR-07 12.00.00 AM          -730486.75


Weird, huh? I have no idea why I get the huge negative number when subtracting the values but that explains the error anyway.
Tom Kyte
May 26, 2007 - 12:13 pm UTC

select to_char( start_time... )
dump(start_time)
and the same for end time for us please.

Helena Marková, May 28, 2007 - 4:13 am UTC

It is very interesting this dump() function.

re: An Avid Reader - Weird DateDiff error

yan, May 29, 2007 - 10:50 am UTC

I've encountered similar results when there was bad date data involved. The year portion was incorrectly converted from a 2 digit year to a 4 digit year.
Using the dump command as Tom suggested or selecting the 4 digit year will show if this is the problem.

SQL> l
  1  select to_date('05-MAR-0007 12:00:00 AM','DD-MON-YYYY HH:MI:SS AM')-
  2         to_date('04-MAR-2007 06:00:00 PM','DD-MON-YYYY HH:MI:SS AM') date_diff
  3* from dual
SQL> /

 DATE_DIFF                                                                                          
----------                                                                                          
-730486.75                                                                                          

re: An Avid Reader - Weird DateDiff error

An Avid Reader, June 04, 2007 - 3:05 pm UTC

Yes, thank you. The application was incorrectly formatting dates on the way in.

Thanks for the reference to the dump() function, Tom. That is a good tool for debugging date problems.

Occurances between a 12-month period

Ramis Shah, July 02, 2007 - 4:35 pm UTC

Hello Gentlemen

I am facing a problem hope you would solve it.


I have a table
CREATE TABLE MATCHES
(MT_ID NUMBER(4) CONSTRAINT MT_ID_PK PRIMARY KEY,
START_DATE DATE,
END_DATE DATE,
TEAM_A NUMBER(2),
TEAM_B NUMBER(2),
SERIES_ID NUMBER(3))

INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,SERIES_ID) VALUES(1,'1 Apr 1993', '5 Apr 1993',1,2,1);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,SERIES_ID) VALUES(2,'21 Apr 1993', '25 Apr 1993',2,1,1);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,SERIES_ID) VALUES(3,'30 Mar 1994', '3 Apr 1994',4,2,1);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,SERIES_ID) VALUES(4,'1 May 1994', '5 May 1994',8,1,1);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,SERIES_ID) VALUES(5,'24 Nov 1994', '28 Nov 1994',5,7,1);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,SERIES_ID) VALUES(6,'31 Mar 1999', '4 Apr 1999',3,6,1);


select * from matches order by 1

MT_ID  START_DATE    END_DATE TEAM_A TEAM_B  SERIES_ID
1  01-APR-93  05-APR-93 1 2          1
2  21-APR-93  25-APR-93 2 1          1
3  30-MAR-94  03-APR-94 4 2          1
4  01-MAY-94  05-MAY-94 8 1          1
5  24-NOV-94  28-NOV-94 5 7          1
6  31-MAR-99  04-APR-99 3 6          1



Where
MT_ID is Match ID which is primary key
stat_date is the start date of the match and end_date is the date on which the match ended.
Each match has two teams i.e Team A and Team B. Each team has its unique ID. So in the above data there are eight teams form

ID 1 to 8.

Duration of match can be taken out if we minus the start date from the end date. For example:

Select END_DATE-START_DATE +1
From MATCHES
/

END_DATE-START_DATE+1 
5 
5 
5 
5 
5 
5 


1 is added to include the start day too.


My requirment is as follows:

For each of the 8 Teams, show
1. How many matches they played in each of the 7 twelve month periods from 1 April 1993 to 31 March 2000? If a match overlaped a 12 month period, then it would be treated as one match each on either side on the 12 month period.

For example, if a match started on 30 March 1994 and ended on 3 Apr 1994 then for the 12-month year starting from 1 Apr 1993 to 31 March 1994 it would be treated as one match and for the 12-month period starting from 1 Apr 1994 to 31 March 1995 it would be treated as another match. Becuase the match was split into 2 + 3 days on either side of the 12-month period.

2. How many days each team was playing a match in each of the 7 twelve month periods from 1 April 1993 to 31 March 2000?

For example, a team played 3 matches between Apr 1, 1993 and Mar 31, 1994. The Duration of each of the matches was 5+5+5 = 15 days. But the third match overlaped the 12-month period which started on 30 Mar 1994 and ended on 3 Apr 1994. So the correct number of playing days for that team would be 5 + 5 + 2 = 12 days from Apr 1, 1993 and Mar 31, 1994.

A want an efficient query that gets me both of the above answers


My desired output is like this:

For Team ID = 1       

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    2    10     
1-Apr-1994 to 31-Mar-1995    1    5
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    0    0
1-Apr-1999 to 31-Mar-2000    0    0


For Team ID = 2       

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    3    12     
1-Apr-1994 to 31-Mar-1995    1    3
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    0    0
1-Apr-1999 to 31-Mar-2000    0    0


For Team ID = 3       

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    0    0     
1-Apr-1994 to 31-Mar-1995    0    0
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    1    1
1-Apr-1999 to 31-Mar-2000    1    4


For Team ID = 4       

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    1    2     
1-Apr-1994 to 31-Mar-1995    1    3
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    0    0
1-Apr-1999 to 31-Mar-2000    0    0


For Team ID = 5      

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    0    0     
1-Apr-1994 to 31-Mar-1995    1    5
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    0    0
1-Apr-1999 to 31-Mar-2000    0    0


For Team ID = 6      

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    0    0     
1-Apr-1994 to 31-Mar-1995    0    0
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    1    1
1-Apr-1999 to 31-Mar-2000    1    4


For Team ID = 7      

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    0    0     
1-Apr-1994 to 31-Mar-1995    1    5
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    0    0
1-Apr-1999 to 31-Mar-2000    0    0


For Team ID = 8      

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    0    0     
1-Apr-1994 to 31-Mar-1995    1    5
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    0    0
1-Apr-1999 to 31-Mar-2000    0    0


I would appreciate a prompt reply in this regard.

Thanks in advance
Ramis Shah

Tom Kyte
July 03, 2007 - 10:26 am UTC

... I would appreciate a prompt reply in this regard. ....

:) that always makes me smile.


ops$tkyte%ORA10GR2> with data
  2  as
  3  (
  4   select sdate, add_months(sdate,12)-1 edate
  5     from
  6   (
  7   select add_months( to_date(:rpt_start,'dd-mon-yyyy'), (level-1)*12 ) sdate
  8     from dual
  9     connect by level <= 7
 10   )
 11  ),
 12  two_rows
 13  as
 14  (select 1 r from dual union all select 2 r from dual)
 15  select team, sdate, edate, count(*) mt, sum(days) days
 16    from (
 17  select data.sdate, data.edate,
 18         matches.start_date, matches.end_date,
 19             least( data.edate, matches.end_date )-greatest( data.sdate, matches.start_date )+1 days,
 20             decode( two_rows.r, 1, team_a, team_b ) team
 21    from data, two_rows, matches
 22   where data.edate >= matches.start_date
 23     and data.sdate <= matches.end_date
 24         )
 25   group by team, sdate, edate
 26   order by team, sdate
 27  /

      TEAM SDATE     EDATE             MT       DAYS
---------- --------- --------- ---------- ----------
         1 01-APR-93 31-MAR-94          2         10
           01-APR-94 31-MAR-95          1          5

         2 01-APR-93 31-MAR-94          3         12
           01-APR-94 31-MAR-95          1          3

         3 01-APR-98 31-MAR-99          1          1
           01-APR-99 31-MAR-00          1          4

         4 01-APR-93 31-MAR-94          1          2
           01-APR-94 31-MAR-95          1          3

         5 01-APR-94 31-MAR-95          1          5

         6 01-APR-98 31-MAR-99          1          1
           01-APR-99 31-MAR-00          1          4

         7 01-APR-94 31-MAR-95          1          5

         8 01-APR-94 31-MAR-95          1          5


13 rows selected.




I'll leave it as an exercise for you if you truly think you need those zero lines in there - it can in fact be done...

Another solution

Michel Cadot, July 03, 2007 - 3:39 pm UTC

SQL> with 
  2    data as (
  3      select decode(trunc(rn/2),0,team_a,team_b) team,
  4             decode(mod(rn,2),
  5                    0,start_date,
  6                    decode(trunc(months_between(start_date,to_date('01/04/1993','DD/MM/YYYY'))/12),
  7                           trunc(months_between(end_date,to_date('01/04/1993','DD/MM/YYYY'))/12),
  8                           start_date,
  9                           add_months(to_date('01/04/1993','DD/MM/YYYY'),
 10                                      12*trunc(months_between(end_date,to_date('01/04/1993','DD/MM/YYYY'))/12))
 11                          )
 12                   ) start_date,
 13             decode(mod(rn,2),
 14                    1,end_date,
 15                    decode(trunc(months_between(start_date,to_date('01/04/1993','DD/MM/YYYY'))/12),
 16                           trunc(months_between(end_date,to_date('01/04/1993','DD/MM/YYYY'))/12),
 17                           end_date,
 18                           add_months(to_date('01/04/1993','DD/MM/YYYY'),
 19                                      12*trunc(months_between(end_date,to_date('01/04/1993','DD/MM/YYYY'))/12))
 20                             - 1)
 21                   ) end_date,
 22             decode(mod(rn,2),
 23                    0, trunc(months_between(start_date,to_date('01/04/1993','DD/MM/YYYY'))/12),
 24                       trunc(months_between(end_date,to_date('01/04/1993','DD/MM/YYYY'))/12)
 25                   ) period         
 26      from matches, 
 27           (select level-1 rn from dual connect by level <= 4)
 28      where mod(rn,2) = 0
 29         or trunc(months_between(start_date,to_date('01/04/1993','DD/MM/YYYY'))/12)
 30            != trunc(months_between(end_date,to_date('01/04/1993','DD/MM/YYYY'))/12)
 31    )
 32  select team, 
 33         add_months(to_date('01/04/1993','DD/MM/YYYY'),12*period) start_period,
 34         add_months(to_date('01/04/1993','DD/MM/YYYY'),12*(period+1))-1 end_period,
 35         count(*) mt,
 36         sum(end_date-start_date+1) days
 37  from data
 38  group by team, period
 39  order by team, start_period
 40  /
      TEAM START_PERIO END_PERIOD          MT       DAYS
---------- ----------- ----------- ---------- ----------
         1 01-APR-1993 31-MAR-1994          2         10
           01-APR-1994 31-MAR-1995          1          5

         2 01-APR-1993 31-MAR-1994          3         12
           01-APR-1994 31-MAR-1995          1          3

         3 01-APR-1998 31-MAR-1999          1          1
           01-APR-1999 31-MAR-2000          1          4

         4 01-APR-1993 31-MAR-1994          1          2
           01-APR-1994 31-MAR-1995          1          3

         5 01-APR-1994 31-MAR-1995          1          5

         6 01-APR-1998 31-MAR-1999          1          1
           01-APR-1999 31-MAR-2000          1          4

         7 01-APR-1994 31-MAR-1995          1          5

         8 01-APR-1994 31-MAR-1995          1          5


13 rows selected.

Regards
Michel

The requested URL /apex/wwv_flow.show was not found on this server

Ramis, July 10, 2007 - 1:09 pm UTC

Hi Tom

I am unable to run a query (below) it as it keeps on giving the following error.

Not found
The requested URL /apex/wwv_flow.show was not found on this server

link of the erroer print screen

http://www5.upload2.net/download/80d58c6c5cb8da74ed439f810680a69b/4693a03e/bWxPogIFBs8Xe11/Error.JPG

I am using XP home edition. Please advise what's wrong with this:

My oracle db is:

BANNER
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

The query is at the end, here is DDL data
CREATE TABLE MATCHES
(MT_ID NUMBER(4) CONSTRAINT MT_ID_PK PRIMARY KEY,
START_DATE DATE,
END_DATE DATE,
TEAM_A NUMBER(2),
TEAM_B NUMBER(2))

INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B) VALUES(1,'1 Apr 1993', '5 Apr 1993',1,2);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B) VALUES(2,'21 Apr 1993', '25 Apr 1993',2,1);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B) VALUES(3,'30 Mar 1994', '3 Apr 1994',4,2);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B) VALUES(4,'1 May 1994', '5 May 1994',8,1);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B) VALUES(5,'24 Nov 1994', '28 Nov 1994',5,7);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B) VALUES(6,'31 Mar 1999', '4 Apr 1999',3,6);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B) VALUES(7,'1 Apr 1999', '5 Apr 1999',7,8);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B) VALUES(8,'29 Mar 2000', '2 Apr 2000',3,6);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B) VALUES(9,'2 Jun 2000', '6 Jun 2000',4,5);



break on team nodup skip 1

with periods as ( select add_months(dt,12*(rownum-1)) start_period,
                             add_months(dt,12*rownum)-1 end_period
                      from (select to_date('1-Apr-1992') dt from dual)
                      connect by level <= 9),
         matches_by_period as ( select start_period, end_period, start_date,
end_date,
                                case when start_date <= end_period and end_date
>= start_period
                                     then least(end_date,end_period) -
greatest(start_date,start_period) + 1
                                     end playing_days, team_a, team_b
                                from periods, matches)
   select decode(grouping_id(team,period), 1, 'TOTAL' ) TOT, team, period,
sum(free_days) freedays,
          max(exists_six_weeks_gap) exists_six_week_gap from (
   select   team
          , to_char(start_period,'dd-Mon-yyyy') || ' to ' ||
            to_char(end_period,'dd-Mon-yyyy') period
          , case when max(end_period) >= max(min_end_dt_team) and
max(start_period) <= max(max_end_dt_team) then
               max(end_dt) -  min(start_dt) - sum(nvl(playing_days,0)) + 1
            else 0 end free_days
          , nvl(case when max(min_end_dt_team) between max(start_period) and
max(end_period) then
                 case when max(days_between_matches) >= 42
                 then 1 else 0 end
            end,0) exists_six_weeks_gap
   from (select   nvl(team_a,team_b) team
                , start_period
                , end_period
                , max(end_date) over (partition by nvl(team_a,team_b))
max_end_dt_team
                , min(end_date) over (partition by nvl(team_a,team_b))
min_end_dt_team
                , playing_days
                , greatest(start_period,start_date) start_dt
                , least(end_period, end_date) end_dt
                , case when playing_days is not null then
                  nvl(start_date - lag(end_date) over
                                      (partition by nvl(team_a,team_b),
start_period, end_period
                                       order by nvl(team_a,team_b),
start_date, end_date), 42) end
                  days_between_matches
         from (select   start_period
                      , end_period
                      , start_date
                      , end_date
                      , team_a
                      , team_b
                      , sum(playing_days) playing_days
               from matches_by_period
               group by start_period, end_period, start_date, end_date,
                       grouping sets (team_a, team_b)
              )
       )
   group by team, to_char(start_period,'dd-Mon-yyyy') || ' to ' ||
                    to_char(end_period,'dd-Mon-yyyy')
   )
   group by rollup(team, period)
   having grouping_id(team,period) <= 1 




months_between strange results

jj, August 27, 2007 - 11:58 am UTC

1 -
It is strange that moving only the month of the beggining date of a period, the fractional part (number of days of a 31-days months) changes.

(I need the period between the two dates included, so I subtract 1).

Example:

select
round((months_between(to_date('15/05/2007','dd-mm-yyyy'),to_date('01/01/2006','dd-mm-yyyy')-1)),10),
round((months_between(to_date('15/05/2007','dd-mm-yyyy'),to_date('01/02/2006','dd-mm-yyyy')-1)),10),
round((months_between(to_date('15/05/2007','dd-mm-yyyy'),to_date('01/03/2006','dd-mm-yyyy')-1)),10),
round((months_between(to_date('15/05/2007','dd-mm-yyyy'),to_date('01/04/2006','dd-mm-yyyy')-1)),10)
from dual;

The whole results:
end/date
start/date 15/05/2007

01/01/2006 16,48387097 ok 16 months and 15/31 days
01/02/2006 15,48387097
01/03/2006 14,58064516 <- ? 14 months and quite 18/31 days
01/04/2006 13,48387097
01/05/2006 12,51612903 <- ? 12 months and quite 16/31
01/06/2006 11,48387097
01/07/2006 10,51612903 <-
01/08/2006 9,483870968
01/09/2006 8,483870968
01/10/2006 7,516129032 <-
01/11/2006 6,483870968
01/12/2006 5,516129032 <-
01/01/2007 4,483870968

Others strange results:
select months_between(to_date('01/03/2006','dd-mm-yyyy'),to_date('28/02/2006','dd-mm-yyyy')) from dual;

0,1290322580645161290322580645161290322581
that in a 31-days month are 4 days instead of 1.


select 31 * months_between(to_date('01/05/2006','dd-mm-yyyy'),to_date('30/04/2006','dd-mm-yyyy')) from dual;

--> 2 dd

2- If I split the period, I have different results for the fractional part:

select
round((months_between(to_date('15/05/2007','dd-mm-yyyy'),to_date('01/03/2006','dd-mm-yyyy')-1)),10),
round((months_between(to_date('15/05/2007','dd-mm-yyyy'),to_date('01/01/2007','dd-mm-yyyy')-1) +months_between(to_date('31/12/2006','dd-mm-yyyy'),to_date('01/03/2006','dd-mm-yyyy')-1)
),10)from dual;


14,5806451613 14,4838709677

How it is possible to solve this problem?



Tom Kyte
September 04, 2007 - 12:10 pm UTC

months_between is documented as always working with a 31 day month by design.


http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/functions089.htm#i78039

...
If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month
......


A month is a moving target - it is not like a "week" (7 days) or a day (24 hours), it is a nebulous thing.


Using simple math - you can get the months between as an integer, adjust the one date using that and subtract to get the number of months and number of days between - and then divide...

ops$tkyte%ORA10GR2> select dt1, dt2, round( months_between(dt1, dt2), 10 ) monthsbtwn,
  2         trunc(months_between(dt1,dt2))+(dt1-add_months(dt2,trunc(months_between(dt1,dt2)) ))/31 otherway
  3    from (select to_date( '15/05/2007', 'dd-mm-yyyy') dt1,
  4                 add_months( to_date( '01/01/2006', 'dd/mm/yyyy' ),level-1)-1 dt2 from dual connect by level <= 15 )
  5  /

DT1       DT2       MONTHSBTWN   OTHERWAY
--------- --------- ---------- ----------
15-MAY-07 31-DEC-05  16.483871  16.483871
15-MAY-07 31-JAN-06  15.483871  15.483871
15-MAY-07 28-FEB-06 14.5806452  14.483871
15-MAY-07 31-MAR-06  13.483871  13.483871
15-MAY-07 30-APR-06  12.516129  12.483871
15-MAY-07 31-MAY-06  11.483871  11.483871
15-MAY-07 30-JUN-06  10.516129  10.483871
15-MAY-07 31-JUL-06 9.48387097 9.48387097
15-MAY-07 31-AUG-06 8.48387097 8.48387097
15-MAY-07 30-SEP-06 7.51612903 7.48387097
15-MAY-07 31-OCT-06 6.48387097 6.48387097
15-MAY-07 30-NOV-06 5.51612903 5.48387097
15-MAY-07 31-DEC-06 4.48387097 4.48387097
15-MAY-07 31-JAN-07 3.48387097 3.48387097
15-MAY-07 28-FEB-07 2.58064516 2.48387097

15 rows selected.


Using between clause

Manju, September 26, 2007 - 6:27 pm UTC

Tom,

I am doing a select from the dba_audit_trail.
I want to retrieve data from the following columns for a specified range of dates based on the Timestamp.I am using ampersand(&) for the &fromtime and &totime. Here is my sql

select username,obj_name,action_name,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP from dba_audit_trail
where timestamp between to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') &FROMTIME and
to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') &TOTIME
/
Here is the result set
##########################
Enter value for fromtime: '30-OCT-2005 00:00:00'
Enter value for totime: '01-NOV-2005 00:00:00'
old 2: where timestamp between to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') &FROMTIME and to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') &TOTIME
new 2: where timestamp between to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') '30-OCT-2005 00:00:00' and to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') '01-NOV-2005 00:00:00'
where timestamp between to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') '30-OCT-2005 00:00:00' and to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') '01-NOV-2005 00:00:00'
*
ERROR at line 2:
ORA-00905: missing keyword
#############################

What am i missing here ?

Thanks,
Manju


Tom Kyte
September 26, 2007 - 10:15 pm UTC

seems that

where timestamp between to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') &FROMTIME 
and
to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') &TOTIME
/

should be

where timestamp between to_char('&fromtime','DD-MON-YYYY HH24:MI:SS') 
and
to_char('&totime','DD-MON-YYYY HH24:MI:SS')
/

Finding difference in minutes between two timestamp columns

Bob, March 06, 2008 - 12:42 pm UTC

Hi Tom,

Suppose I have a table defined like this:

SQL> desc  T_JACOFACRELEASETABLE

 PAYMENTREF                   NOT NULL NUMBER
 CREATEDUSERREF                        NUMBER
 DATERELEASED                          TIMESTAMP(6)
 LASTUPDATEDUSERREF                    NUMBER
 LASTUPDATED                           TIMESTAMP(6)
 OFACRELEASED                          NUMBER

Now I want to find all paymentref's that have a datereleased
more than five minutes ago.

How can I do that using SQL with a timestamp column? 

Tom Kyte
March 07, 2008 - 5:05 pm UTC

ops$tkyte%ORA10GR2> create table t ( x timestamp, y timestamp );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2      for i in 1 .. 7
  3      loop
  4          insert into t(x) values ( systimestamp );
  5          dbms_lock.sleep(1);
  6      end loop;
  7
  8      update t set y = systimestamp;
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select x, y, y-x
  2    from t
  3  /

X                            Y                            Y-X
---------------------------- ---------------------------- ----------------------------
07-MAR-08 05.07.28.825031 PM 07-MAR-08 05.07.35.867900 PM +000000000 00:00:07.042869
07-MAR-08 05.07.29.826685 PM 07-MAR-08 05.07.35.867900 PM +000000000 00:00:06.041215
07-MAR-08 05.07.30.826920 PM 07-MAR-08 05.07.35.867900 PM +000000000 00:00:05.040980
07-MAR-08 05.07.31.828508 PM 07-MAR-08 05.07.35.867900 PM +000000000 00:00:04.039392
07-MAR-08 05.07.32.829632 PM 07-MAR-08 05.07.35.867900 PM +000000000 00:00:03.038268
07-MAR-08 05.07.33.830995 PM 07-MAR-08 05.07.35.867900 PM +000000000 00:00:02.036905
07-MAR-08 05.07.34.831064 PM 07-MAR-08 05.07.35.867900 PM +000000000 00:00:01.036836

7 rows selected.

ops$tkyte%ORA10GR2> select x, y, y-x
  2    from t
  3   where y-x > numtodsinterval(5,'second')
  4  /

X                            Y                            Y-X
---------------------------- ---------------------------- ----------------------------
07-MAR-08 05.07.28.825031 PM 07-MAR-08 05.07.35.867900 PM +000000000 00:00:07.042869
07-MAR-08 05.07.29.826685 PM 07-MAR-08 05.07.35.867900 PM +000000000 00:00:06.041215
07-MAR-08 05.07.30.826920 PM 07-MAR-08 05.07.35.867900 PM +000000000 00:00:05.040980


Comparison of two dates

Bob, March 07, 2008 - 5:24 pm UTC

Hi Tom - that's absolutely fantastic. I never knew about numtodsinterval and its usage!

Thank you so much!

Datediff

Sagar, June 10, 2008 - 1:25 pm UTC

I have written this datediff  as the month is always moving sometimes 28 , 29 , 30 or 31.


CREATE OR REPLACE procedure datediff(big_date in date , small_date in date,years out number,months out number , days out number)
is
pl_big_date date;
pl_small_date date;
pl_years number;
pl_months number;
pl_days number;
begin
days:=0;
months:=0;
years:=0;
pl_big_date:=trunc(big_date);
pl_small_date := trunc(small_date);
while(pl_small_date <= pl_big_date)
loop

days:=days+1;
pl_small_date := pl_small_date + 1 ;

--dbms_output.put_line('days ' || days);
if(pl_small_Date > add_months(small_date,1+months + (12 * years) )) then

days:=0;
months:=months+1;
--dbms_output.put_line('months ' ||  months);
end if;
if(pl_small_Date > add_months(small_date,12+ (12 * years))) then
days:=0;
months:=0;
--dbms_output.put_line('years  ' || years );
years:=years+1;
end if;
end loop;
end;
/

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2    BIG_DATE DATE;
  3    SMALL_DATE DATE;
  4    pl_YEARS NUMBER;
  5    pl_MONTHS NUMBER;
  6    pl_dAYS NUMBER;
  7  BEGIN
  8    BIG_DATE := '10-jun-2008';
  9    SMALL_DATE := '30-nov-1978';
 10    SCOTT.DATEDIFF ( BIG_DATE, SMALL_DATE, pl_YEARS, pl_MONTHS, pl_DAYS );
 11    dbms_output.put_line(pl_years);
 12    dbms_output.put_line(pl_months);
 13    dbms_output.put_line(pl_days);
 14    COMMIT;
 15* END;
SQL> /
29
6
10

PL/SQL procedure successfully completed.


Tom Kyte
June 10, 2008 - 2:52 pm UTC

explain to me the logic:

14 COMMIT;


why why why would you do that -EVER.

ugh. that is the worst - the pits. I hope you don't throw spurious commits in your code like that in real life.

I've no idea what your "datediff" is or does or why it is better. We are humans, not compilers. If you don't explain it - it doesn't count.

So what if there are different days in the month? the builtins sort of *deal* with that. You seem to have written a lot of procedural code to do the same thing as a couple of builtin functions

Sagar, June 11, 2008 - 11:50 am UTC

Argh!...that commit...not required at all...(forgot to comment that, some famous code generator puts the commit there).

The logic is just trying to put in a code as how would we manually calculate difference in two dates for e.g. age by counting days , months and years.

Tom Kyte
June 11, 2008 - 4:00 pm UTC

but - the builtin functions do that, can you tell us why doing it with lots of code is "better"?

Sagar, June 12, 2008 - 12:59 am UTC

Just different way of doing it thats all.I have used builtins in my code as much as possible in my logic.


Tom Kyte
June 12, 2008 - 7:35 am UTC

compare:

ops$tkyte@8i> select months_between( end_date, start_date ),
  2                  trunc( months_between( end_date, start_date ) /12 ) Years,
  3                  mod( trunc( months_between( end_date, start_date ) ), 12 ) 
months,
  4                  end_date - add_months(start_date,trunc( months_between( 
end_date, start_date ) )) days
  5  from t
  6  /



to

CREATE OR REPLACE procedure datediff(big_date in date , small_date in date,years out number,months 
out number , days out number)
is
pl_big_date date;
pl_small_date date;
pl_years number;
pl_months number;
pl_days number;
begin
days:=0;
months:=0;
years:=0;
pl_big_date:=trunc(big_date);
pl_small_date := trunc(small_date);
while(pl_small_date <= pl_big_date)
loop

days:=days+1;
pl_small_date := pl_small_date + 1 ;

--dbms_output.put_line('days ' || days);
if(pl_small_Date > add_months(small_date,1+months + (12 * years) )) then

days:=0;
months:=months+1;
--dbms_output.put_line('months ' ||  months);
end if;
if(pl_small_Date > add_months(small_date,12+ (12 * years))) then
days:=0;
months:=0;
--dbms_output.put_line('years  ' || years );
years:=years+1;
end if;
end loop;
end;
/



you need no procedural logic to

a) design
b) code
c) test
d) maintain

the builtin functions *do it all*, there is absolutely NO NEED to write any procedural code. Your approach should be actively discouraged, it is not the right approach at all. It is hugely wasteful of resources (cpu), not very understandable.

given that your 35 lines of code could be:

ops$tkyte%ORA10GR2> CREATE OR REPLACE procedure datediff(end_date in date , start_date in date,years out number,months out number , days out number)
  2  is
  3  begin
  4          years := trunc( months_between( end_date, start_date ) /12 );
  5          months := mod( trunc( months_between( end_date, start_date ) ), 12 );
  6          days := end_date - add_months(start_date,trunc( months_between( end_date, start_date ) ));
  7  end;
  8  /


why would you do it that way?


Yeah got it thnx!!!...

Sagar, June 12, 2008 - 8:07 am UTC

I agree on that...no doubts...never claimed it to be better...we try to do things good way and still we find ways to do it better way.

Calc Number Of Hours

Ahmad Darwish, November 16, 2008 - 9:45 am UTC

Dear Tom.
I'Have Two Fields format is Date
Fist one labe is Stat_Date := 10-jan-2008 10:20:12 am
Second one labe is End_Date := 11-jan-2008 01:15:20 pm

now
how can get the period between to date in (Hours:Minute)

Thanks allot.

Tom Kyte
November 18, 2008 - 7:18 pm UTC

ops$tkyte%ORA10GR2> select sdate, edate,
  2         trunc(  (edate-sdate) * 24 ) hours,
  3             ((edate-sdate)*24 - trunc(  (edate-sdate) * 24 ))*60  minutes
  4    from (
  5  select to_date( '10-jan-2008 10:20:12 am', 'dd-mon-yyyy hh:mi:ss am' ) sdate,
  6         to_date( '11-jan-2008 01:15:20 pm', 'dd-mon-yyyy hh:mi:ss am' ) edate
  7    from dual
  8         )
  9  /

SDATE     EDATE          HOURS    MINUTES
--------- --------- ---------- ----------
10-JAN-08 11-JAN-08         26 55.1333333


A reader, January 28, 2009 - 9:23 am UTC

Tom,
select systimestamp col,
(systimestamp-5/1440) minus_5,
(systimestamp+5/1440) plu_5
from dual;

I would like to get (systimestamp - 5 minutes) and
(systimestamp + 5 minutes) and display in timestamp format.

When I subtract 5 mins or add 5 minutes, it is displayed as MM/DD/YYYY HH:MI:SS AM. How to get it in timestamp format?
COL,MINUS_5,PLU_5
1/28/2009 9:09:10.993631 AM -05:00,1/28/2009 9:04:10 AM,1/28/2009 9:14:10 AM
Tom Kyte
January 28, 2009 - 3:55 pm UTC


ops$tkyte%ORA11GR1> select systimestamp, systimestamp+numtodsinterval( 5, 'minute' ), systimestamp-numtodsinterval( 5, 'minute' ) from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
SYSTIMESTAMP+NUMTODSINTERVAL(5,'MINUTE')
---------------------------------------------------------------------------
SYSTIMESTAMP-NUMTODSINTERVAL(5,'MINUTE')
---------------------------------------------------------------------------
28-JAN-09 03.53.48.092440 PM -05:00
28-JAN-09 03.58.48.092440000 PM -05:00
28-JAN-09 03.48.48.092440000 PM -05:00



use intervals...

date arithmetic works with DATES.. Intervals can be added to timestamps

date difference

A reader, February 11, 2009 - 5:09 am UTC

Hi Tom, i was looking for the time difference between two dates based on certain condition. I need to calculate the total troubleshoot duration(closure-troubleshoot) for a given ticket.For the below example it should give me 3 hrs but not 5 hrs. Can you please help.

Ticket status changedate
I1086 TRBLESHOOT 2/8/2008 9:40 am
I1086 CLOSURE 2/8/2008 10:40 am
I1086 TRBLESHOOT 2/8/2008 11.40 am
I1086 CLOSURE 2/8/2008 12:40 pm
I1086 TRBLESHOOT 2/8/2008 1:40 pm
I1086 CLOSURE 2/8/2008 2:40 pm

Tom Kyte
February 11, 2009 - 10:39 am UTC

no create table
no inserts
no look

and make it "interesting", one would presume there is more than one trouble ticket in the database

and make it more clear, phrase requirements in form of, well, requirements. for example I think you mean:

a) find the min change date for a ticket where status = trbleshoot
b) find the max change date for a ticket where status = closure
c) expected result is (b)-(c), for each ticket


(and you know what, if you phrase it that way, it almost answers itself - no?)

A reader, February 12, 2009 - 12:28 am UTC

Tom, thanks a lot for your response..:)
Apologies for not very clear of the requirement.Actually I require the data from the History table where we store the different statuses that a ticket undergoes. For Example:

Table Attribute

Ticket Ticket,status,changedate,TicketUid(unique)
Tktstatus(History)Ticket,status,changedate,Tktstatusid(unique)

where Ticket.Ticket equals Tktstatus.Ticket

Any change in STATUS of the Ticket table will be captured in History table Tktstatus.
Below is the data from Tktstatus - History table.

Ticket status changedate Tktstatusid
I1086 TRBLESHOOT 2/8/2008 9:40 am 1
I1086 CLOSURE 2/8/2008 10:40 am 2
I1086 TRBLESHOOT 2/8/2008 11.40 am 3
I1086 CLOSURE 2/8/2008 12:40 pm 4
I1086 TRBLESHOOT 2/8/2008 1:40 pm 5
I1086 CLOSURE 2/8/2008 2:40 pm 6


Max(closure) - Min(Trbleshoot) gives me 5 Hrs which is wrong.
I require the exact duration the ticket was in Troubleshoot duration.
ie., 1st ( CLOSURE minus TRBLESHOOT) + 2nd + 3rd
(2/8/2008 9:40 ¿ 2/8/2008 10.40) + (2/8/2008 11:40 ¿ 2/8/2008 12.40)+ (2/8/2008 1:40 ¿ 2/8/2008 2.40)
which is 1 + 1 + 1 = 3 Hrs

I have taken the duration in seconds.But this query gives me cartesian product. How to avoid those...please help.
select a.ticket,
trunc((b.changedate-a.changedate)*24*60*60) as Duration
from tktstatus a, tktstatus b
where a.ticket = b.ticket
b.changedate > a.changedate
and a. status = 'TRBLESHOOT'
and b.status = 'CLOSURE'

Thanks much.



Tom Kyte
February 12, 2009 - 12:13 pm UTC

no create table
no inserts
no look

A reader, February 13, 2009 - 3:58 am UTC

Tom,

Please use the below scripts.

CREATE TABLE TKTSTATUS
(
TICKETID VARCHAR2(10) NOT NULL,
STATUS VARCHAR2(10) NOT NULL,
CHANGEBY VARCHAR2(30) NOT NULL,
CHANGEDATE DATE NOT NULL,
TKSTATUSID NUMBER NOT NULL
);
Insert into TKTSTATUS
(TICKETID, STATUS, CHANGEBY, CHANGEDATE, TKSTATUSID)
Values
('I1086', 'TRBLESHOOT', 'MAXADMIN', TO_DATE('2/8/2008 9:40:40', 'MM/DD/YYYY HH24:MI:SS'), 1);
Insert into TKTSTATUS
(TICKETID, STATUS, CHANGEBY, CHANGEDATE, TKSTATUSID)
Values
('I1086', 'CLOSURE', 'MAXADMIN', TO_DATE('2/8/2008 10:40:40', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into TKTSTATUS
(TICKETID, STATUS, CHANGEBY, CHANGEDATE, TKSTATUSID)
Values
('I1086', 'TRBLESHOOT', 'MAXADMIN', TO_DATE('2/8/2008 11:40:40', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into TKTSTATUS
(TICKETID, STATUS, CHANGEBY, CHANGEDATE, TKSTATUSID)
Values
('I1086', 'CLOSURE', 'MAXADMIN', TO_DATE('2/8/2008 12:40:40', 'MM/DD/YYYY HH24:MI:SS'), 4);
Insert into TKTSTATUS
(TICKETID, STATUS, CHANGEBY, CHANGEDATE, TKSTATUSID)
Values
('I1086', 'TRBLESHOOT', 'MAXADMIN', TO_DATE('2/8/2008 13:40:40', 'MM/DD/YYYY HH24:MI:SS'), 5);
Insert into TKTSTATUS
(TICKETID, STATUS, CHANGEBY, CHANGEDATE, TKSTATUSID)
Values
('I1086', 'CLOSURE', 'MAXADMIN', TO_DATE('2/8/2008 14:40:40', 'MM/DD/YYYY HH24:MI:SS'), 6);

Required: The exact duration the ticket was in Troubleshoot duration.
ie., 1st ( CLOSURE minus TRBLESHOOT) + 2nd + 3rd
(2/8/2008 9:40:40 - 2/8/2008 10:40:40) + (2/8/2008 11:40:40 - 2/8/2008 12:40:40)+ (2/8/2008 13:40:40 - 2/8/2008 14:40:40)

Hope this helps.

Thanks for your patience.

















Tom Kyte
February 16, 2009 - 10:31 am UTC

guess you missed this entirely:

... and make it "interesting", one would presume there is more than one trouble ticket in the database ....

oh well, hard to test things without boundary conditions and interesting data.

<b>
marry up each record with the 'next' record by changedate.  Note: this will all assume that TRBLESHOOT, CLOSURE are the only things in this table, if not - you know what to do - ADD A PREDICATE to only get trbleshoot, closure records....

and you must decide what to do if you have a trbleshoot record that is NOT followed by a closure record - you will need to default next_changedate to a value that pleases  you (maybe sysdate?  I don't know, insufficient data to tell you want to do there....)

</b>
ops$tkyte%ORA10GR2> select ticketid,
  2         status, changedate,
  3         lead(status) over (partition by ticketid order by changedate) next_status,
  4         lead(changedate) over (partition by ticketid order by changedate) next_changedate
  5    from tktstatus
  6  /

TICKETID   STATUS     CHANGEDATE           NEXT_STATU NEXT_CHANGEDATE
---------- ---------- -------------------- ---------- --------------------
I1086      TRBLESHOOT 08-feb-2008 09:40:40 CLOSURE    08-feb-2008 10:40:40
I1086      CLOSURE    08-feb-2008 10:40:40 TRBLESHOOT 08-feb-2008 11:40:40
I1086      TRBLESHOOT 08-feb-2008 11:40:40 CLOSURE    08-feb-2008 12:40:40
I1086      CLOSURE    08-feb-2008 12:40:40 TRBLESHOOT 08-feb-2008 13:40:40
I1086      TRBLESHOOT 08-feb-2008 13:40:40 CLOSURE    08-feb-2008 14:40:40
I1086      CLOSURE    08-feb-2008 14:40:40

6 rows selected.


<b>then just keep the interesting records...</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select ticketid, status, next_status, next_changedate,changedate
  2    from (
  3  select ticketid,
  4         status, changedate,
  5         lead(status) over (partition by ticketid order by changedate) next_status,
  6         lead(changedate) over (partition by ticketid order by changedate) next_changedate
  7    from tktstatus
  8         )
  9   where status = 'TRBLESHOOT'
 10  /

TICKETID   STATUS     NEXT_STATU NEXT_CHANGEDATE      CHANGEDATE
---------- ---------- ---------- -------------------- --------------------
I1086      TRBLESHOOT CLOSURE    08-feb-2008 10:40:40 08-feb-2008 09:40:40
I1086      TRBLESHOOT CLOSURE    08-feb-2008 12:40:40 08-feb-2008 11:40:40
I1086      TRBLESHOOT CLOSURE    08-feb-2008 14:40:40 08-feb-2008 13:40:40

<b>and aggregate</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select ticketid, sum(next_changedate-changedate)*24*60*60 seconds
  2    from (
  3  select ticketid,
  4         status, changedate,
  5         lead(status) over (partition by ticketid order by changedate) next_status,
  6         lead(changedate) over (partition by ticketid order by changedate) next_changedate
  7    from tktstatus
  8         )
  9   where status = 'TRBLESHOOT'
 10   group by ticketid
 11  /

TICKETID      SECONDS
---------- ----------
I1086           10800


Excellent Sir

Gita, February 17, 2009 - 8:44 am UTC

Awesome..!!! Thanks a lot Tom.. :) the way you narrate and solve the queries indeed helps beginners like me a lot to understand the database at ease :)

For your comment,
"... and make it "interesting", one would presume there is more than one trouble ticket in the database ...."
.... actually the data is from Ticket History table where we would capture each status change that a particular ticket undergoes..Hence there is more than one ticket of the same id.

You are right...as you guessed, there exists other statuses also for the ticket along with trbleshoot and closure.

FOr example

Ticket status changedate Tktstatusid
I1086 CREATION 2/8/2008 9:40 am 1
I1086 ACCEPTANCE 2/8/2008 10:40 am 2
I1086 TRBLESHOOT 2/8/2008 11.40 am 3
I1086 CLOSURE 2/8/2008 12:40 pm 4
I1086 TRBLESHOOT 2/8/2008 1:40 pm 5
I1086 CLOSURE 2/8/2008 2:40 pm 6
I1086 CLOSED 2/8/2008 3:40 pm 7

You have told me to use predicate function to get the trbleshoot and closure records...can u pls show me how to use that function for the above scenario..

Now i need to calculate the total Troubleshoot duration and need to display the result in DD HH:MM:SS format(day hours:minute:seconds).

Please use the below Scripts

CREATE TABLE TKTSTATUS
(
TICKETID VARCHAR2(10) NOT NULL,
STATUS VARCHAR2(10) NOT NULL,
CHANGEBY VARCHAR2(30) NOT NULL,
CHANGEDATE DATE NOT NULL,
TKSTATUSID NUMBER NOT NULL
);
Insert into TKTSTATUS
(TICKETID, STATUS, CHANGEBY, CHANGEDATE, TKSTATUSID)
Values
('I1086', 'CREATION', 'MAXADMIN', TO_DATE('2/8/2008 9:40:40', 'MM/DD/YYYY HH24:MI:SS'), 1);
Insert into TKTSTATUS
(TICKETID, STATUS, CHANGEBY, CHANGEDATE, TKSTATUSID)
Values
('I1086', 'ACCEPTANCE', 'MAXADMIN', TO_DATE('2/8/2008 10:40:40', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into TKTSTATUS
(TICKETID, STATUS, CHANGEBY, CHANGEDATE, TKSTATUSID)
Values
('I1086', 'TRBLESHOOT', 'MAXADMIN', TO_DATE('2/8/2008 11:40:40', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into TKTSTATUS
(TICKETID, STATUS, CHANGEBY, CHANGEDATE, TKSTATUSID)
Values
('I1086', 'CLOSURE', 'MAXADMIN', TO_DATE('2/8/2008 12:40:40', 'MM/DD/YYYY HH24:MI:SS'), 4);
Insert into TKTSTATUS
(TICKETID, STATUS, CHANGEBY, CHANGEDATE, TKSTATUSID)
Values
('I1086', 'TRBLESHOOT', 'ADMIN', TO_DATE('2/8/2008 13:40:40', 'MM/DD/YYYY HH24:MI:SS'), 5);
Insert into TKTSTATUS
(TICKETID, STATUS, CHANGEBY, CHANGEDATE, TKSTATUSID)
Values
('I1086', 'CLOSURE', 'ADMIN', TO_DATE('2/8/2008 14:40:40', 'MM/DD/YYYY HH24:MI:SS'), 6);

Insert into TKTSTATUS
(TICKETID, STATUS, CHANGEBY, CHANGEDATE, TKSTATUSID)
Values
('I1086', 'CLOSED', 'MAXADMIN', TO_DATE('2/8/2008 14:40:40', 'MM/DD/YYYY HH24:MI:SS'), 7);

Thanks in advance.








Tom Kyte
February 17, 2009 - 8:58 am UTC

you do not know how to add a where clause?

where status in ( 'a', 'b')

where a and b are the open/close events - just add that to the innermost query.

Thanks

A reader, February 18, 2009 - 10:04 pm UTC

Thanks Tom

date diff

Harschil Kaparwan, March 31, 2009 - 8:19 am UTC

Hi Tom,

Many thanks for your help.

I have :

ZZ@MYDB9I:XXXX> desc t1;
 Name                     Null?    Type
------------------------- -------- -----------------------------
 OBJECT_NAME                       VARCHAR2(128)
 CREATED                           DATE
 LAST_DDL_TIME                     DATE
 DAY_LIGHT_SAVING_IN_SEC           NUMBER

ZZ@MYDB9I:XXXX>


with data :

INSERT INTO T1 ( OBJECT_NAME, CREATED, LAST_DDL_TIME,
DAY_LIGHT_SAVING_IN_SEC ) VALUES ( 
'RUNSTATS_PKG',  TO_Date( '03/28/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),  TO_Date( '03/29/2009 03:59:59 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 3600); 
INSERT INTO T1 ( OBJECT_NAME, CREATED, LAST_DDL_TIME,
DAY_LIGHT_SAVING_IN_SEC ) VALUES ( 
'C_OBJ#',  TO_Date( '03/28/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),  TO_Date( '03/29/2008 12:30:30 PM', 'MM/DD/YYYY HH:MI:SS AM')
, 3600); 
COMMIT;


ZZ@MYDB9I:XXXX> select * from t1;

OBJECT_NAME     CREATED             LAST_DDL_TIME       DAY_LIGHT_SAVING_IN_SEC
------------ -------------------    ------------------- --------------
RUNSTATS_PKG   28/03/2009 00:00:00  29/03/2009 03:59:59 3600
C_OBJ#         28/03/2008 00:00:00  29/03/2008 12:30:30 3600

ZZ@MYDB9I:XXXX>

Note: DAY_LIGHT_SAVING_IN_SEC , a number datatype stores the daylight saving in  seconds
       

to calculate the difference in hours for 
 (last_ddl_time)-(created) , including DAY_LIGHT_SAVING_IN_SEC subtraction 
i did 
 

ZZ@MYDB9I:XXXX> select object_name,created,last_ddl_time,day_light_saving_in_sec,  ((last_ddl_time-created)*24*60*60-DAY_LIGHT_SAVING_
IN_SEC)/60/60 DDL_MISSED_HRS from t1;

OBJECT_NAME        CREATED             LAST_DDL_TIME       DAY_LIGHT_SAVING_IN_SEC DDL_MISSED_HRS
------------------ ------------------- ---------------     ----------------        --------------
RUNSTATS_PKG       28/03/2009 00:00:00 29/03/2009 03:59:59 3600                    26.9997222
C_OBJ#             28/03/2008 00:00:00 29/03/2008 12:30:30 3600                    35.5083333

ZZ@MYDB9I:XXXX> 
 
i wanted  to display the output ((last_ddl_time-created)*24*60*60-DAY_LIGHT_SAVING_IN_SEC ,  which is in seconds as :
<m> days, <n> hrs, <o> mins, <p> secs format

Kindly help.

Regards

Harschil Kaparwan 

Tom Kyte
March 31, 2009 - 9:09 am UTC

interesting

did you even read the original answer? Follow any of the links?

date diff .......

Harschil Kaparwan, March 31, 2009 - 9:33 am UTC

Yes Tom,

I read the original answer posted by you.
But could not make it for days, hrs, mins & secs.

also i have slightly different requirement here..
i am subtracting the column DAY_LIGHT_SAVING_IN_SEC, which is in seconds, also from the result set of two dates (last_ddl_time)-(created) and want to display the entire result in

<m> days, <n> hrs, <o> mins, <p> secs format


Many thanks for your time.

Regards

Harschil Kaparwan




Tom Kyte
April 01, 2009 - 7:01 am UTC

and the link? in the original answer? to a query that shows precisely how to do this?

You have a difference in dates A and B

(a-b) - seconds *1/24/60


follow the LINK as suggested and discover a query that shows precisely how to do this. In that query, when you follow that link, where I use

(sysdate-created)

you'll use

((a-b)-seconds*1/24/60)

instead

date diff .......

Harschil, April 01, 2009 - 8:20 am UTC

Hi Tom,

my fault, i didnt scanned the link properly.

Extremally sorry for making you to re-visit my question.

Kind Regards

Harschil

Months Between

Sachin, May 13, 2009 - 2:55 pm UTC

============================================================
Followup   September 1, 2004 - 8am US/Eastern:

ops$tkyte@ORA9IR2> select d1, d2, add_months(trunc(d1,'mm'),r)
  2    from t1, (select rownum-1 r
  3                from all_objects
  4               where rownum <= ( select max(months_between(d2,d1))+1 from t1) )
  5   where months_between(d2,d1)+1 >= r
  6  /

============================================================

I was expecting to see 3 rows for date d1='25-Mar-2009' and date d2='07-May-2009', but this SQL gives only two rows,one for March and one for April. Am I missing something here?

Thanks as always for your insightful replies.

Regards
Sachin

Tom Kyte
May 13, 2009 - 3:38 pm UTC

ops$tkyte%ORA10GR2> select d1, d2, add_months(trunc(d1,'mm'),r)
  2    from t1, (select rownum-1 r
  3                from all_objects
  4               where rownum <= ( select max(months_between(trunc(d2,'mm'),trunc(d1,'mm')))+1 from t1) )
  5    where months_between(d2,d1)+1 >= r
  6  /

D1        D2        ADD_MONTH
--------- --------- ---------
25-MAR-09 07-MAY-09 01-MAR-09
25-MAR-09 07-MAY-09 01-APR-09
25-MAR-09 07-MAY-09 01-MAY-09


indeed, should have backed the start/end times off to the beginning of the month to make the math work properly when the start date is near the end of the month and the end date was near the beginning

Months Between

Sachin, May 13, 2009 - 6:45 pm UTC

Wonderful!

Thanks Tom!

A reader, December 14, 2009 - 5:51 am UTC

CREATE TABLE SCOTT.TEST_DATA
(
FROM_DATE DATE,
TO_DATE DATE,
ID NUMBER(2)
);

SET DEFINE OFF;
Insert into TEST_DATA
(FROM_DATE, TO_DATE, ID)
Values
(TO_DATE('12/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1);
COMMIT;

if required all Tuesday then as follows

Tuesday

12/01/2009
12/08/2009
12/15/2009

If required all saturday and monday then follows
Saturday, Monday

12/05/2009
12/07/2009
12/12/2009
12/14/2009
Tom Kyte
December 14, 2009 - 9:53 am UTC

lovely.

Too bad we have no clue what you are trying to convey. Whether it be a question or whatever - there is no clue as to what you are saying...

A reader, December 15, 2009 - 1:15 am UTC

Sorry Tom

Want to get all the Saturday and Monday between dates (12/01/2009 and 12/15/2009 )

12/05/2009
12/07/2009
12/12/2009
12/14/2009
Tom Kyte
December 15, 2009 - 9:51 am UTC

Insert into TEST_DATA
   (FROM_DATE, TO_DATE, ID)
 Values
   (TO_DATE('12/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/2009 00:00:00', 
'MM/DD/YYYY HH24:MI:SS'), 1);


I'm gobsmacked. How do you get from start/end of 1-dec, 31-dec to "just to the 15th please"

Seriously.

ops$tkyte%ORA9IR2> variable strt varchar2(30)
ops$tkyte%ORA9IR2> variable end  varchar2(30)
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec :strt := '1-dec-2009'; :end := '15-dec-2009';

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> alter session set nls_date_format = 'dd-mon-yyyy Day';

Session altered.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> with data
  2  as
  3  (select level-1 l
  4     from dual
  5  connect by level <=
  6      to_date( :end, 'dd-mon-yyyy' ) - to_date( :strt, 'dd-mon-yyyy' )+1 )
  7  select to_date( :strt, 'dd-mon-yyyy' )+l
  8    from data
  9   where to_char( to_date(:strt,'dd-mon-yyyy')+l, 'dy' ) in ('sat','mon')
 10  /

TO_DATE(:STRT,'DD-MON
---------------------
05-dec-2009 Saturday
07-dec-2009 Monday
12-dec-2009 Saturday
14-dec-2009 Monday

ops$tkyte%ORA9IR2> with data
  2  as
  3  (select level-1 l
  4     from dual
  5  connect by level <=
  6      to_date( :end, 'dd-mon-yyyy' ) - to_date( :strt, 'dd-mon-yyyy' )+1 )
  7  select to_date( :strt, 'dd-mon-yyyy' )+l
  8    from data
  9   where to_char( to_date(:strt,'dd-mon-yyyy')+l, 'd' )
 10      in ( to_char( to_date( '15-03-1965', 'dd-mm-yyyy' ), 'd' ),
 11           to_char( to_date( '13-03-1965', 'dd-mm-yyyy' ), 'd' ) )
 12  /

TO_DATE(:STRT,'DD-MON
---------------------
05-dec-2009 Saturday
07-dec-2009 Monday
12-dec-2009 Saturday
14-dec-2009 Monday




the second one is more international as it doesn't rely on English.

A reader, December 16, 2009 - 7:51 am UTC

can you please explain use of "with data as"

wrong century for the dates from flat file

A reader, January 06, 2010 - 8:43 pm UTC

I am in the process of uploading the data from flat file ( aka CSV) into oracle via external table .
All the dates have the year of 20xx. My understanding was 51-99 will be prefixed with 19 ( such as 1951 - 1999) and 00-50
will be prefixed with 20 ( such as 2004 ... )

The column below ( startdate ) is of timestamp .

Is my understanding wrong ?

SQL> select startdate , to_date(startdate , 'dd/mm/yyyy' ) newdate from tab;


NEW_DATE   STARTDATE                 
---------- ------------------------- 
09/18/2090 18-SEP-90 12.00.00.000000000 AM  



BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> show parameter nls

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_calendar                         string
nls_comp                             string      BINARY
nls_currency                         string
nls_date_format                      string
nls_date_language                    string
nls_dual_currency                    string
nls_iso_currency                     string
nls_language                         string      AMERICAN
nls_length_semantics                 string      BYTE
nls_nchar_conv_excp                  string      FALSE
nls_numeric_characters               string
nls_sort                             string
nls_territory                        string      AMERICA
nls_time_format                      string
nls_time_tz_format                   string
nls_timestamp_format                 string
nls_timestamp_tz_format              string
SQL>
SQL>

Tom Kyte
January 11, 2010 - 8:09 am UTC

... Is my understanding wrong ? ...

yes.


If you give me a fully century and year - such as 2090 AS INPUT, and say "would you please convert that into century and year", what did you think would happen???

You have 2090 as input.

You already have *2090* as input.

2090 is a century plus year.


Also, yyyy says explicitly, "I'm giving you a century and year".

I think you want RR
And I think you want to leave the century off, if you want us to figure it out.

ops$tkyte%ORA10GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

ops$tkyte%ORA10GR2> select to_date( '90', 'rr' ) from dual;

TO_DATE('90','RR')
--------------------
01-jan-1990 00:00:00

ops$tkyte%ORA10GR2> select to_date( '10', 'rr' ) from dual;

TO_DATE('10','RR')
--------------------
01-jan-2010 00:00:00


A reader, January 07, 2010 - 8:54 pm UTC

For the above post , this is the corrected SQL.

select to_char(startdate , 'mm/dd/yyyy') sdate, startdate from t

SDATE STARTDATE
-----------------------------------
09/18/2090 18-SEP-90 12.00.00.000000 AM


In the flat file , I have the value as 091890 for the startdate .
Tom Kyte
January 11, 2010 - 8:51 pm UTC

select to_date( '091890', 'mmddrr' ) from table;

ops$tkyte%ORA10GR2> select to_date( '091890', 'mmddrr' ) from dual
  2  /

TO_DATE('091890','MM
--------------------
18-sep-1990 00:00:00



I don't know why you would have

a) '/' in there, you KNOW you don't have any???!?!?!?!

b) use yyyy - you KNOW you have yy at best, and RR is what you want...

To: "A reader" - Re: External table

Kim Berg Hansen, January 12, 2010 - 3:09 am UTC

Hi, "A reader"

You state you use external table to import your flat file. Your flat file have the string "091890". Your "tab" (in your first question, in the second you call it "t") must be your external table, where "startdate" is a timestamp column holding the data from the column in the flat file containing "091890".

What you need to do is to use MMDDRR in your definition of the column "startdate" in your external table. It is the format model in the external table column definition that matters for what century the "startdate" column will have. If you use MMDDYY you will always get "current century", but RR works as Tom says :-)

Difference between two dates grouped by calendar year

Ahmad, January 13, 2010 - 3:05 am UTC

CREATE TABLE EMPS_LEAVE
(
ID NUMBER(10),
LEAVE_ID NUMBER(3) ,
EMP_ID NUMBER(10),
START_DATE DATE ,
END_DATE DATE
);

SET DEFINE OFF;
Insert into MOD.EMPS_LEAVE
(ID, LEAVE_ID, EMP_ID, START_DATE, END_DATE)
Values
(1, 900, 1000, TO_DATE('12/16/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/09/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MOD.EMPS_LEAVE
(ID, LEAVE_ID, EMP_ID, START_DATE, END_DATE)
Values
(2, 900, 2000, TO_DATE('12/12/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/10/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MOD.EMPS_LEAVE
(ID, LEAVE_ID, EMP_ID, START_DATE, END_DATE)
Values
(3, 901, 1000, TO_DATE('01/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/31/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

how to write a SQL statement to group the difference between the two dates per calendar year .... result set like this

emp_id leave_id duration calendar_year
1000 900 16 2009
1000 900 9 2010
1000 901 31 2009
2000 900 20 2009
2000 900 10 2010


Thanks
Tom Kyte
January 18, 2010 - 4:13 pm UTC

ops$tkyte%ORA10GR2> with data as (select level r from dual connect by level <= 2)
  2  select emp_id, leave_id,
  3         decode( r, 1, start_date, trunc(end_date,'y') ) sdate,
  4             decode( r, 2, end_date, least( add_months(trunc(start_date,'y'),12)-1, end_date ) ) edate,
  5             start_date, end_date
  6    from emps_leave, data
  7   where trunc(start_date,'y') <> trunc(end_date,'y') or r = 1
  8   order by emp_id, leave_id
  9  /

    EMP_ID   LEAVE_ID SDATE     EDATE     START_DAT END_DATE
---------- ---------- --------- --------- --------- ---------
      1000        900 16-DEC-09 31-DEC-09 16-DEC-09 09-JAN-10
      1000        900 01-JAN-10 09-JAN-10 16-DEC-09 09-JAN-10
      1000        901 01-JAN-09 31-JAN-09 01-JAN-09 31-JAN-09
      2000        900 12-DEC-09 31-DEC-09 12-DEC-09 10-JAN-10
      2000        900 01-JAN-10 10-JAN-10 12-DEC-09 10-JAN-10

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with data as (select level r from dual connect by level <= 2)
  2  select emp_id, leave_id, sum(edate-sdate+1) duration, to_char(sdate,'yyyy') calendar_year
  3    from (
  4  select emp_id, leave_id,
  5         decode( r, 1, start_date, trunc(end_date,'y') ) sdate,
  6             decode( r, 2, end_date, least( add_months(trunc(start_date,'y'),12)-1, end_date ) ) edate
  7    from emps_leave, data
  8   where trunc(start_date,'y') <> trunc(end_date,'y') or r = 1
  9         )
 10   group by emp_id, leave_id, to_char( sdate,'yyyy')
 11   order by emp_id, leave_id
 12  /

    EMP_ID   LEAVE_ID   DURATION CALE
---------- ---------- ---------- ----
      1000        900         16 2009
      1000        900          9 2010
      1000        901         31 2009
      2000        900         20 2009
      2000        900         10 2010




note: check the boundary conditions - verify lines 5 and 6 are correct. And if you have to say "but I don't know how to tell if they are correct", you need to sit down first and figure out what this query does :)

basically - turns every row in the original set into one or two rows....

if start date and end date are in the same year, you get one row, else you get two.

then we do magic on the start end dates for row 1 and row 2 to set their values.

then it is simple from there.

Difference between two dates grouped by calendar year

MarkusR, January 15, 2010 - 6:32 am UTC

Hi Ahmed,

maybe this is one solution to your Problem:
SELECT emp_id,
       leave_id,
       end_date - add_months(start_date, trunc(months_between(end_date, start_date))) + 1 AS duration,
       c_year
  FROM (SELECT (2008 + LEVEL) AS c_year,
               emp_id,
               leave_id,
               CASE 
                WHEN start_date > to_date('01/01/' || (2008 + LEVEL), 'MM/DD/YYYY') THEN
                start_date
               ELSE
                to_date('01/01/' || (2008 + LEVEL), 'MM/DD/YYYY')
               END AS start_date, 
               CASE 
                WHEN end_date > to_date('31.12.' || (2008 + LEVEL), 'DD.MM.YYYY')  THEN
                to_date('12/31/' || (2008 + LEVEL), 'MM/DD/YYYY')
               ELSE
                end_date
               END AS end_date
          FROM emps_leave t
        CONNECT BY LEVEL <= 2)
 WHERE end_date - add_months(start_date, trunc(months_between(end_date, start_date))) > 0
 GROUP BY emp_id, leave_id, end_date - add_months(start_date, trunc(months_between(end_date, start_date))), c_year
order by 1,2,3

EMP_ID  LEAVE_ID DURATION YEAR
1000 900  9   2010
1000 900  16   2009
1000 901  31   2009
2000 900  10   2010
2000 900  20   2009


Best regards,
Markus

AW: Difference between two dates grouped by calendar year

MarkusR, January 15, 2010 - 8:51 am UTC

Hi again,

think this is better, sorry for my last Review.
SELECT emp_id,
       leave_id,
       end_date -           
         add_months(start_date,
          trunc(months_between(end_date,start_date)
                    )) + 1 AS duration,
       c_year
  FROM (SELECT (2008 + l_id) AS c_year,
               emp_id,
               leave_id,
               CASE
                 WHEN start_date > to_date('01/01/' || (2008 + l_id), 'MM/DD/YYYY') THEN
                  start_date
                 ELSE
                  to_date('01/01/' || (2008 + l_id), 'MM/DD/YYYY')
               END AS start_date,
               CASE
                 WHEN end_date > to_date('31.12.' || (2008 + l_id), 'DD.MM.YYYY') THEN
                  to_date('12/31/' || (2008 + l_id), 'MM/DD/YYYY')
                 ELSE
                  end_date
               END AS end_date
          FROM emps_leave t,
               (SELECT LEVEL AS l_id
                  FROM dual
                CONNECT BY LEVEL <= 2) g)
 WHERE end_date - add_months(start_date, trunc(months_between(end_date, start_date))) > 0
 ORDER BY 1,
          2,
          4


Best regards,
Markus

A reader, March 03, 2010 - 12:37 am UTC

Want to get minimum time portion from "From_Date" and maximum time portion from "To_Date" this should be independent of date only time should be considers.
CREATE TABLE TEST_DATA
(
FROM_DATE DATE,
TO_DATE DATE
);
SET DEFINE OFF;
Insert into TEST_DATA
(FROM_DATE, TO_DATE)
Values
(TO_DATE('03/03/2010 07:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/03/2010 19:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST_DATA
(FROM_DATE, TO_DATE)
Values
(TO_DATE('12/01/2009 08:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/15/2009 11:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

Tom Kyte
March 03, 2010 - 6:59 am UTC

ops$tkyte%ORA10GR2> select min(to_char(from_date, 'hh24:mi:ss')), max(to_char(to_date,'hh24:mi:ss')) from test_data;

MIN(TO_C MAX(TO_C
-------- --------
07:00:00 19:00:00


dates

A reader, March 03, 2010 - 9:16 am UTC


A reader, March 03, 2010 - 9:39 pm UTC

from above table "TEST_DATA" how we can get difference in minutes between from_date and to_date ignorning date part only using time portion of it.
Tom Kyte
March 04, 2010 - 9:44 am UTC

ops$tkyte%ORA11GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

ops$tkyte%ORA11GR2> select from_date, to_date,
  2         abs( to_date( to_char(from_date, 'hh24:mi:ss'), 'hh24:mi:ss' )-
  3              to_date( to_char(  to_date, 'hh24:mi:ss'), 'hh24:mi:ss' ) ) * 24 * 60 mins
  4    from test_data;

FROM_DATE            TO_DATE                    MINS
-------------------- -------------------- ----------
03-mar-2010 07:00:00 03-mar-2010 19:00:00        720
01-dec-2009 08:00:00 15-dec-2009 11:00:00        180
03-mar-2010 07:00:00 03-mar-2010 19:00:00        720
01-dec-2009 08:00:00 15-dec-2009 11:00:00        180
03-mar-2010 07:00:00 03-mar-2010 19:00:00        720
01-dec-2009 08:00:00 15-dec-2009 11:00:00        180

6 rows selected.

Date format

Sandeep Deokar, May 31, 2010 - 8:04 am UTC

Hi Tom 
I have a multiple upstream and those up streams are sending me date in different format 

Eg one is sending me mm/dd/yyyy other is sending me dd/mon/yyyy one is sending me dd/mm/yyyy

My original date format is 

SQL> select sysdate from dual;

SYSDATE
---------
31-MAY-10

I have to insert these dates in a column but it is giving me an error

SQL> SELECT to_date(TO_CHAR('1/1/2009'),'DD-Mon-YYYY') FROM DUAL
  2  /
SELECT to_date(TO_CHAR('1/1/2009'),'DD-Mon-YYYY') FROM DUAL
               *
ERROR at line 1:
ORA-01843: not a valid month

Please suggest what ever is the input it should take date in DD-Mon-YYYY format.

I dont want to write SQL function which will use substr which will check for input dates and then convert in the required date format.

Please suggest.

Tom Kyte
May 31, 2010 - 8:47 am UTC

My original date format is 

SQL> select sysdate from dual;

SYSDATE
---------
31-MAY-10


I see NO date format there, is that dd-mon-yy, fmdd-mon-yyyy, dd-mon-rr - what??

(not relevant to your question, just saying - there is no date format in your example, none, you are as ambiguous as everyone else)

You need to use a date format of mm/dd/yyyy for the data from one system.
and dd/mon/yyyy from the other
and dd/mm/yyyy from yet the other

In fact, I would suggest using FXmm/dd/yyyy to avoid data errors for the one
fxdd/mon/yyyy
fxdd/mm/yyyy


I dont want to write SQL function which will use substr which will check for
input dates and then convert in the required date format.


good because that would be totally lame, you need to develop a framework for your application that knows that "if the data comes from, then format is A, if from Y then format is B and so on"

so you can add new formats, change them, whatever over time.

Week

A reader, September 28, 2010 - 3:58 am UTC

in first respone of this question you are calculating YEARS MONTHS DAYS i need these

YEARS MONTHS WEEKS DAYS

How can i?
Tom Kyte
September 28, 2010 - 8:23 am UTC

think about it and start dividing ....

take the work I've performed which demonstrates a concept (date arithmetic) and take it one step further to accomplish your unique and specific needs. that is what programmers do.

Learn and understand the technique (you see me adding and subtracting and dividing - that is all you need to do) and apply it to your problem


Look at my first example, one of the rows had 23 days. If you take 23 and divide by 7 - you get 3.something. Truncate it, that is three weeks. Now if you subtract from 23 - 3*7 you get 2. You have 3 weeks and 2 days...

not working

venkata, July 21, 2011 - 2:35 am UTC

Hi

many links like these http://asktom.oracle.com/~tkyte/Misc/DateDiff.html mentioned in your comments don't work, can you make these work :)

thank you
Tom Kyte
July 22, 2011 - 1:46 pm UTC

Tom you are great

Muzafar Jatoi, July 27, 2011 - 3:19 am UTC

Tom,

You are last hope for all DBA & Developers.
You help many yong DBA & Developers.

I solute you on your best work to help others getting time from your busy schedule.


Regards,
Muzafar
Oracle DBA

Negative number of days returned

Livio, February 06, 2013 - 7:44 am UTC

Hello Tom,

I would like to come back to one of the comments at the beginning of this thread, namely the fact that the function that returns the difference between two dates may as well return a negative number of days.

I have been trying out this alternative

select extract(year from years_month) yy, extract(month from years_month) mm, extract(day from days_hours)dd
from (
select numtoyminterval(trunc(months_between(dt2, dt1)), 'month') years_month,
numtodsinterval(dt2-add_months(dt1, trunc(months_between(dt2, dt1))), 'day') days_hours
from (
select to_date('28/02/2002', 'dd/mm/yyyy') dt1, to_date('27/03/2004', 'dd/mm/yyyy') + 1 dt2
from dual
)
)

but with no success so far, with the provided data.

I wonder if there is any solution or workaround. Could you provide it or direct me to the relevant documentation?

Thanks in advance for your help.

Livio
Tom Kyte
February 06, 2013 - 2:05 pm UTC

what do you think the answer should be refer back to my comments above, what is the *right* answer)

I think you just take the greatest( 0, days ).


our "convention" is the same day of two different months is 1 month. 15-feb to 15-mar is one month. so is the last day of a month to the next - 28-feb to 31-mar is one month. Months and days don't "go together" well.


tell me - what is the right answer and why ?

Negative number of days returned for adjacent days

Livio, February 07, 2013 - 3:38 am UTC

Hello Tom.

Thank you for your reply.
The following table recaps what's puzzling me.

Start date End date Years Months Days Mycomment
28-02-2002 26-03-2004 2 0 27 OK
28-02-2002 27-03-2004 2 1 -3 ? (0 m 28 d)
28-02-2002 28-03-2004 2 1 -2 ? (0 m 29 d)
28-02-2002 29-03-2004 2 1 -1 ? (0 m 30 d)
28-02-2002 30-03-2004 2 1 0 OK
28-02-2002 31-03-2004 2 1 1 OK
28-02-2002 01-04-2004 2 1 2 OK

You see, by simply zeroing out negative days, we get the same number of days for March 27, 28, 29 and 30. That doesn't seem reasonable to me, does it? I would expect the results put in brackets or, if a new month is supposed to start at 27-03-2004, the following day should report a day more and so on.

Maybe this is an issue that has to do with leap years (in fact, February 2004 was a leap year)

Thank you in advance for your feedback

Best regards

Livio
Tom Kyte
February 11, 2013 - 8:07 am UTC

what is the result you would like?

the problem lies in the fact that a month is a nebulous thing - it is not a precise number of days.

Feb-N to Mar-N is commonly accepted as a "month". for example Feb-15th to Mar-15th is a month.

So, Feb-28 to Mar-28 is a month.


last_day(feb) to last_day(mar) is also commonly accepted as a "month". So Feb-28 (non-leap year) to Mar-31 is a month.


So, should Feb-28 to Mar 28 be a month and then Feb-28 to Mar-29 be a month and a day? and Feb-28 to Mar-30 a month and two days? and then Feb-28 to Mar-31 a month again? That doesn't seem reasonable to me.

of Feb-28 to Mar 28 be a month and then Feb-28 to Mar-29 be 30 days again? not a month? but it has to be a month of feb-15 to mar-15 is considered a month.


but remember that feb-28 to mar-28 has to be a month, just like feb-27 to mar-27 is a month.

tell me what you would like and we'll get you that answer. I say just zero out the negative days. It is as accurate and meaningful as any other answer.

Negative number of days returned

Livio, February 12, 2013 - 10:26 am UTC

Hello Tom.
Firstly, thanks for taking the time to reply.
The following table summarizes what I want.

Start date End date Years Months Days
28-02-2002 26-03-2004 2 0 27
28-02-2002 27-03-2004 2 0 28
28-02-2002 28-03-2004 2 0 29
28-02-2002 29-03-2004 2 0 30
28-02-2002 30-03-2004 2 1 0
28-02-2002 31-03-2004 2 1 1
28-02-2002 01-04-2004 2 1 2

So I came up with the following query:

SELECT yy,
CASE WHEN dd < 0 THEN GREATEST(0, mm - 1) ELSE mm END mm,
CASE WHEN dd < 0 THEN TO_NUMBER(TO_CHAR(end_of_month, 'dd')) + dd ELSE dd END dd
FROM (
SELECT EXTRACT(YEAR FROM years_month) yy, EXTRACT(MONTH FROM years_month) mm, EXTRACT(DAY FROM days_hours)dd, end_of_month
FROM (
SELECT NUMTOYMINTERVAL(TRUNC(MONTHS_BETWEEN(dt2, dt1)), 'month') years_month,
NUMTODSINTERVAL(dt2 - ADD_MONTHS(dt1, TRUNC(MONTHS_BETWEEN(dt2, dt1))), 'day') days_hours, LAST_DAY(dt2) end_of_month
FROM (SELECT p_start_date dt1, p_end_date dt2 FROM dual)
)
);

I will appreciate any feedback.
What puzzles me about zeroing out the number of negative days is that we can have the same reported result for different days.

Best regards

Livio



Tom Kyte
February 12, 2013 - 11:17 am UTC

so, how do you rectify that feb-15 to mar-15 is a month

but feb-28 to mar-28 is not a month?

that goes against all of the rules.


... What puzzles me about zeroing out the number of negative days is that we can
have the same reported result for different days. ...


that should be obvious - a month is not a fixed period of time!!!!

If someone says "see you in a month" - how many days is that?

Getting First Day of all the months between two years

Suma, June 10, 2013 - 2:37 am UTC

Hi Tom

I am new to these date functions, kindly help me

I am giving input as Two years(Fromyear and Toyear),
Between these two years I need to get the first date of all the months which fall in between these two years.

01-05-2012 (mm-dd-yy) as Fromyear
01-05-2013 (mm-dd-yy) as Toyear

My result should be
01-05-2012
01-06-2012
01-07-2012
till 01-05-2013

Please do help me.

Thanks
Suma.

Tom Kyte
June 18, 2013 - 2:31 pm UTC

you ask for the first day but then post the 5th, 6th, 7th? I'm not sure what your output should be - I'll go with the first day of each month.

ops$tkyte%ORA11GR2> variable sdate varchar2(30)
ops$tkyte%ORA11GR2> variable edate varchar2(30)
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec :sdate := '01-05-2012'; :edate := '01-06-2013';

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select add_months( trunc( sdate, 'mm' ), level-1 ), edate
  2    from (
  3  select to_date( :sdate, 'mm-dd-yyyy' ) sdate, to_date( :edate, 'mm-dd-yyyy' ) edate
  4    from dual
  5         )
  6   where add_months( trunc(sdate,'mm'), level-1) <= edate
  7  connect by level <= ceil( months_between( edate, sdate ) )+1
  8  /

ADD_MONTH EDATE
--------- ---------
01-JAN-12 06-JAN-13
01-FEB-12 06-JAN-13
01-MAR-12 06-JAN-13
01-APR-12 06-JAN-13
01-MAY-12 06-JAN-13
01-JUN-12 06-JAN-13
01-JUL-12 06-JAN-13
01-AUG-12 06-JAN-13
01-SEP-12 06-JAN-13
01-OCT-12 06-JAN-13
01-NOV-12 06-JAN-13
01-DEC-12 06-JAN-13
01-JAN-13 06-JAN-13

13 rows selected.