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

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? 

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;
/

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

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 ?

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. 

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

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.

 

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






 

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
 


 

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

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.

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,

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

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!)

 

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 

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.

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

Thanks!

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

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

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) 

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

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. 

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

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

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" 

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

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
 
 

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

  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
 

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

Thank you very much Tom !

>> 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')

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. 

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.

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 )

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

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.

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 

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

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.

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

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

(meaning Greatest Function).

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

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')

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

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 

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
       ^^                   ^^ 
 

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?

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:

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

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

over 'hh24miss'?

Thanks

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

css

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. 

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

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

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.

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.
 

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

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 

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

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

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.
 

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

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

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

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 :) 

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?

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;

<quote from a book being released soon, if the link 

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.
 

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.

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;

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

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. 

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

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.

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>

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

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.
 

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

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

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.
 

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

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?

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 !

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 

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

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

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;

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.

It is very interesting this dump() function.

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                                                                                          

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.

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

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.

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

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 

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?

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.

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

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

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

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? 

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

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

Thank you so much!

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.

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.

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

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  /

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;
/

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  /

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.

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.

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

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

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

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, 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,

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.

<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

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.

Thanks Tom

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 

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

Hi Tom,

my fault, i didnt scanned the link properly.

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

Kind Regards

Harschil

============================================================
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

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

Wonderful!

Thanks Tom!

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

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

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);

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

can you please explain use of "with data as"

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>

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

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 .

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

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

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 :-)

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

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

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

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

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;

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

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.

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.

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.

My original date format is 

SQL> select sysdate from dual;

SYSDATE
---------
31-MAY-10

in first respone of this question you are calculating YEARS MONTHS DAYS i need these

YEARS MONTHS WEEKS DAYS

How can i?

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,

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

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

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

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

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.

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.

like ID on Bank

Assuming that date1 and date2 are included in the period , try a period from 13-12-2023 till 12-12-2024, (366) days.

The query will return 11.97 months , 0 years , 11 months and 29 days.

The difference between two dates should allow us to do round trips; in other words, the start date plus the difference should return the end date and the end date minus the difference should return the start date.

Using ADD_MONTHS we lose information: for example, add a month to any date from January 28th on and we get the same result, so subtracting a month doesn't get us back to the original start date.

Intervals work differently: they don't allow us to add a month to January 31st.

Here is a solution using interval expressions that handles dates or timestamps and allows round trips:
1) If the "from" day-to-second portion is not greater than the "to" portion, is it easy to separate a YEAR TO MONTH portion and a DAY TO SECOND portion
2) In other cases, whenever possible subtract a month from the YEAR TO MONTH portion and add to the DAY TO SECOND portion the number of days in the month preceding the "to" date.
3) When 2) is not an option, leave the portions as is and accept that the day-to-second portion will be negative!

To make testing easier, here is a view that generates the question

create or replace view question as  
with dates(dte) as (  
  select date '2023-01-26' + numtodsinterval(level,'day')
  from dual  
  connect by level <= 35
)  
, ranges(date_from, date_to) as (  
  select a.dte, b.dte + 1.31/24
  from dates a, dates b  
  where a.dte < b.dte - 25
)  
select * from ranges
order by 1,2;

In the answer, I separate the different date/time portions and I recalculate the from and to dates.

with data as (  
  select date_from, date_to,  
    (trunc(date_to,'mm') - trunc(date_from,'mm')) year(5) to month iym,
    (date_from - trunc(date_from, 'mm')) day to second from_ids,
    (date_to - trunc(date_to, 'mm')) day to second to_ids,
    extract(day from date_from) from_day,
    extract(day from trunc(date_to, 'mm') - 1) prev_to_day
  from question
)  
, new_intervals as (
  select date_from, date_to,
    case when from_ids > to_ids and from_day <= prev_to_day
      then (iym - interval '1' month)
      else iym
    end new_iym,
    case when from_ids > to_ids and from_day <= prev_to_day
      then to_ids - from_ids + numtodsinterval(prev_to_day, 'day')
      else to_ids - from_ids
    end new_ids
  from data
)
select date_from, date_to, 
  extract(year from new_iym) yy,
  extract(month from new_iym) mm,
  extract(day from new_ids) dd,
  extract(hour from new_ids) hh24,
  extract(minute from new_ids) mi,
  extract(second from new_ids) ss,
  date_to - new_ids - new_iym same_date_from,
  date_from + new_iym + new_ids same_date_to
from new_intervals
order by date_from, date_to

DATE_FROM              DATE_TO                YY    MM     DD    HH24     MI     SS SAME_DATE_FROM         SAME_DATE_TO           
2023-01-27 00:00:00    2023-02-22 01:18:36     0     0     26       1     18     36 2023-01-27 00:00:00    2023-02-22 01:18:36    
2023-01-27 00:00:00    2023-02-23 01:18:36     0     0     27       1     18     36 2023-01-27 00:00:00    2023-02-23 01:18:36    
2023-01-27 00:00:00    2023-02-24 01:18:36     0     0     28       1     18     36 2023-01-27 00:00:00    2023-02-24 01:18:36    
2023-01-27 00:00:00    2023-02-25 01:18:36     0     0     29       1     18     36 2023-01-27 00:00:00    2023-02-25 01:18:36    
2023-01-27 00:00:00    2023-02-26 01:18:36     0     0     30       1     18     36 2023-01-27 00:00:00    2023-02-26 01:18:36    
2023-01-27 00:00:00    2023-02-27 01:18:36     0     1      0       1     18     36 2023-01-27 00:00:00    2023-02-27 01:18:36    
2023-01-27 00:00:00    2023-02-28 01:18:36     0     1      1       1     18     36 2023-01-27 00:00:00    2023-02-28 01:18:36    
2023-01-27 00:00:00    2023-03-01 01:18:36     0     1      2       1     18     36 2023-01-27 00:00:00    2023-03-01 01:18:36    
2023-01-27 00:00:00    2023-03-02 01:18:36     0     1      3       1     18     36 2023-01-27 00:00:00    2023-03-02 01:18:36    
2023-01-28 00:00:00    2023-02-23 01:18:36     0     0     26       1     18     36 2023-01-28 00:00:00    2023-02-23 01:18:36    
2023-01-28 00:00:00    2023-02-24 01:18:36     0     0     27       1     18     36 2023-01-28 00:00:00    2023-02-24 01:18:36    
2023-01-28 00:00:00    2023-02-25 01:18:36     0     0     28       1     18     36 2023-01-28 00:00:00    2023-02-25 01:18:36    
2023-01-28 00:00:00    2023-02-26 01:18:36     0     0     29       1     18     36 2023-01-28 00:00:00    2023-02-26 01:18:36    
2023-01-28 00:00:00    2023-02-27 01:18:36     0     0     30       1     18     36 2023-01-28 00:00:00    2023-02-27 01:18:36    
2023-01-28 00:00:00    2023-02-28 01:18:36     0     1      0       1     18     36 2023-01-28 00:00:00    2023-02-28 01:18:36    
2023-01-28 00:00:00    2023-03-01 01:18:36     0     1      1       1     18     36 2023-01-28 00:00:00    2023-03-01 01:18:36    
2023-01-28 00:00:00    2023-03-02 01:18:36     0     1      2       1     18     36 2023-01-28 00:00:00    2023-03-02 01:18:36    
2023-01-29 00:00:00    2023-02-24 01:18:36     0     0     26       1     18     36 2023-01-29 00:00:00    2023-02-24 01:18:36    
2023-01-29 00:00:00    2023-02-25 01:18:36     0     0     27       1     18     36 2023-01-29 00:00:00    2023-02-25 01:18:36    
2023-01-29 00:00:00    2023-02-26 01:18:36     0     0     28       1     18     36 2023-01-29 00:00:00    2023-02-26 01:18:36    
2023-01-29 00:00:00    2023-02-27 01:18:36     0     0     29       1     18     36 2023-01-29 00:00:00    2023-02-27 01:18:36    
2023-01-29 00:00:00    2023-02-28 01:18:36     0     0     30       1     18     36 2023-01-29 00:00:00    2023-02-28 01:18:36    
2023-01-29 00:00:00    2023-03-01 01:18:36     0     2    -27     -22    -41    -24 2023-01-29 00:00:00    2023-03-01 01:18:36    
2023-01-29 00:00:00    2023-03-02 01:18:36     0     2    -26     -22    -41    -24 2023-01-29 00:00:00    2023-03-02 01:18:36    
2023-01-30 00:00:00    2023-02-25 01:18:36     0     0     26       1     18     36 2023-01-30 00:00:00    2023-02-25 01:18:36    
2023-01-30 00:00:00    2023-02-26 01:18:36     0     0     27       1     18     36 2023-01-30 00:00:00    2023-02-26 01:18:36    
2023-01-30 00:00:00    2023-02-27 01:18:36     0     0     28       1     18     36 2023-01-30 00:00:00    2023-02-27 01:18:36    
2023-01-30 00:00:00    2023-02-28 01:18:36     0     0     29       1     18     36 2023-01-30 00:00:00    2023-02-28 01:18:36    
2023-01-30 00:00:00    2023-03-01 01:18:36     0     2    -28     -22    -41    -24 2023-01-30 00:00:00    2023-03-01 01:18:36    
2023-01-30 00:00:00    2023-03-02 01:18:36     0     2    -27     -22    -41    -24 2023-01-30 00:00:00    2023-03-02 01:18:36    
2023-01-31 00:00:00    2023-02-26 01:18:36     0     0     26       1     18     36 2023-01-31 00:00:00    2023-02-26 01:18:36    
2023-01-31 00:00:00    2023-02-27 01:18:36     0     0     27       1     18     36 2023-01-31 00:00:00    2023-02-27 01:18:36    
2023-01-31 00:00:00    2023-02-28 01:18:36     0     0     28       1     18     36 2023-01-31 00:00:00    2023-02-28 01:18:36    
2023-01-31 00:00:00    2023-03-01 01:18:36     0     2    -29     -22    -41    -24 2023-01-31 00:00:00    2023-03-01 01:18:36    
2023-01-31 00:00:00    2023-03-02 01:18:36     0     2    -28     -22    -41    -24 2023-01-31 00:00:00    2023-03-02 01:18:36    
2023-02-01 00:00:00    2023-02-27 01:18:36     0     0     26       1     18     36 2023-02-01 00:00:00    2023-02-27 01:18:36    
2023-02-01 00:00:00    2023-02-28 01:18:36     0     0     27       1     18     36 2023-02-01 00:00:00    2023-02-28 01:18:36    
2023-02-01 00:00:00    2023-03-01 01:18:36     0     1      0       1     18     36 2023-02-01 00:00:00    2023-03-01 01:18:36    
2023-02-01 00:00:00    2023-03-02 01:18:36     0     1      1       1     18     36 2023-02-01 00:00:00    2023-03-02 01:18:36    
2023-02-02 00:00:00    2023-02-28 01:18:36     0     0     26       1     18     36 2023-02-02 00:00:00    2023-02-28 01:18:36    
2023-02-02 00:00:00    2023-03-01 01:18:36     0     0     27       1     18     36 2023-02-02 00:00:00    2023-03-01 01:18:36    
2023-02-02 00:00:00    2023-03-02 01:18:36     0     1      0       1     18     36 2023-02-02 00:00:00    2023-03-02 01:18:36    
2023-02-03 00:00:00    2023-03-01 01:18:36     0     0     26       1     18     36 2023-02-03 00:00:00    2023-03-01 01:18:36    
2023-02-03 00:00:00    2023-03-02 01:18:36     0     0     27       1     18     36 2023-02-03 00:00:00    2023-03-02 01:18:36    
2023-02-04 00:00:00    2023-03-02 01:18:36     0     0     26       1     18     36 2023-02-04 00:00:00    2023-03-02 01:18:36    

Here is a somewhat different solution (using "start_date , end_date" instead of "date_from, date_to"):
1) If start_date is earlier in its month than end_date is in its month, simply separate the YEAR TO MONTH difference and the DAY TO SECOND difference
2) Or if start_date is earlier in its month than the last day of the month prior to end_date, then subtract a month from the YEAR TO MONTH difference and add the prior month's days to the DAY TO SECOND difference
3) Otherwise, calculate the differences starting from the end_date, not the start_date. The YEAR TO MONTH difference will be negative, and round trips require applying the differences in another order.

create or replace view question as  
with dates(dte) as (  
  select date '2023-01-26' + numtodsinterval(level,'day')
  from dual  
  connect by level <= 35
)  
, ranges(start_date, end_date) as (  
  select a.dte, b.dte + 1/3
  from dates a, dates b  
  where a.dte < b.dte - 25
)  
select * from ranges
order by 1,2;

with data as (  
  select start_date, end_date,  
    (trunc(end_date,'mm') - trunc(start_date,'mm')) year(5) to month iym,
    (trunc(start_date,'mm') - trunc(end_date,'mm')) year(5) to month reverse_iym,
    (start_date - trunc(start_date, 'mm')) day to second start_ids,
    (end_date - trunc(end_date, 'mm')) day to second end_ids,
    extract(day from start_date) start_day,
    extract(day from trunc(end_date, 'mm') - 1) prev_last_day
  from question
)  
, new_intervals as (
  select start_date, end_date, start_ids, end_ids, start_day, prev_last_day,
    case
      when start_ids <= end_ids
        then iym
      when start_day <= prev_last_day
          then (iym - interval '1' month)
      else reverse_iym
    end new_iym,
    case
      when start_ids <= end_ids
        then end_ids - start_ids
      when start_day <= prev_last_day
          then end_ids - start_ids + numtodsinterval(prev_last_day, 'day')
      else start_ids - end_ids
    end new_ids
  from data
)
select start_date, end_date, 
  extract(year from new_iym) yy,
  extract(month from new_iym) mm,
  extract(day from new_ids) dd,
  extract(hour from new_ids) hh24,
  extract(minute from new_ids) mi,
  extract(second from new_ids) ss,
  case when start_ids > end_ids and start_day > prev_last_day
    then end_date + new_iym + new_ids
    else end_date - new_ids - new_iym 
  end same_start_date,
  case when start_ids > end_ids and start_day > prev_last_day
    then start_date - new_ids - new_iym
    else start_date + new_iym + new_ids 
  end same_end_date
from new_intervals
order by start_date, end_date
/
START_DATE           END_DATE             YY  MM  DD HH24  MI  SS SAME_START_DATE      SAME_END_DATE      
2023-01-27 00:00:00  2023-02-22 08:00:00   0   0  26    8   0   0 2023-01-27 00:00:00  2023-02-22 08:00:00
2023-01-27 00:00:00  2023-02-23 08:00:00   0   0  27    8   0   0 2023-01-27 00:00:00  2023-02-23 08:00:00
2023-01-27 00:00:00  2023-02-24 08:00:00   0   0  28    8   0   0 2023-01-27 00:00:00  2023-02-24 08:00:00
2023-01-27 00:00:00  2023-02-25 08:00:00   0   0  29    8   0   0 2023-01-27 00:00:00  2023-02-25 08:00:00
2023-01-27 00:00:00  2023-02-26 08:00:00   0   0  30    8   0   0 2023-01-27 00:00:00  2023-02-26 08:00:00
2023-01-27 00:00:00  2023-02-27 08:00:00   0   1   0    8   0   0 2023-01-27 00:00:00  2023-02-27 08:00:00
2023-01-27 00:00:00  2023-02-28 08:00:00   0   1   1    8   0   0 2023-01-27 00:00:00  2023-02-28 08:00:00
2023-01-27 00:00:00  2023-03-01 08:00:00   0   1   2    8   0   0 2023-01-27 00:00:00  2023-03-01 08:00:00
2023-01-27 00:00:00  2023-03-02 08:00:00   0   1   3    8   0   0 2023-01-27 00:00:00  2023-03-02 08:00:00
2023-01-28 00:00:00  2023-02-23 08:00:00   0   0  26    8   0   0 2023-01-28 00:00:00  2023-02-23 08:00:00
2023-01-28 00:00:00  2023-02-24 08:00:00   0   0  27    8   0   0 2023-01-28 00:00:00  2023-02-24 08:00:00
2023-01-28 00:00:00  2023-02-25 08:00:00   0   0  28    8   0   0 2023-01-28 00:00:00  2023-02-25 08:00:00
2023-01-28 00:00:00  2023-02-26 08:00:00   0   0  29    8   0   0 2023-01-28 00:00:00  2023-02-26 08:00:00
2023-01-28 00:00:00  2023-02-27 08:00:00   0   0  30    8   0   0 2023-01-28 00:00:00  2023-02-27 08:00:00
2023-01-28 00:00:00  2023-02-28 08:00:00   0   1   0    8   0   0 2023-01-28 00:00:00  2023-02-28 08:00:00
2023-01-28 00:00:00  2023-03-01 08:00:00   0   1   1    8   0   0 2023-01-28 00:00:00  2023-03-01 08:00:00
2023-01-28 00:00:00  2023-03-02 08:00:00   0   1   2    8   0   0 2023-01-28 00:00:00  2023-03-02 08:00:00
2023-01-29 00:00:00  2023-02-24 08:00:00   0   0  26    8   0   0 2023-01-29 00:00:00  2023-02-24 08:00:00
2023-01-29 00:00:00  2023-02-25 08:00:00   0   0  27    8   0   0 2023-01-29 00:00:00  2023-02-25 08:00:00
2023-01-29 00:00:00  2023-02-26 08:00:00   0   0  28    8   0   0 2023-01-29 00:00:00  2023-02-26 08:00:00
2023-01-29 00:00:00  2023-02-27 08:00:00   0   0  29    8   0   0 2023-01-29 00:00:00  2023-02-27 08:00:00
2023-01-29 00:00:00  2023-02-28 08:00:00   0   0  30    8   0   0 2023-01-29 00:00:00  2023-02-28 08:00:00
2023-01-29 00:00:00  2023-03-01 08:00:00   0  -2  27   16   0   0 2023-01-29 00:00:00  2023-03-01 08:00:00
2023-01-29 00:00:00  2023-03-02 08:00:00   0  -2  26   16   0   0 2023-01-29 00:00:00  2023-03-02 08:00:00
2023-01-30 00:00:00  2023-02-25 08:00:00   0   0  26    8   0   0 2023-01-30 00:00:00  2023-02-25 08:00:00
2023-01-30 00:00:00  2023-02-26 08:00:00   0   0  27    8   0   0 2023-01-30 00:00:00  2023-02-26 08:00:00
2023-01-30 00:00:00  2023-02-27 08:00:00   0   0  28    8   0   0 2023-01-30 00:00:00  2023-02-27 08:00:00
2023-01-30 00:00:00  2023-02-28 08:00:00   0   0  29    8   0   0 2023-01-30 00:00:00  2023-02-28 08:00:00
2023-01-30 00:00:00  2023-03-01 08:00:00   0  -2  28   16   0   0 2023-01-30 00:00:00  2023-03-01 08:00:00
2023-01-30 00:00:00  2023-03-02 08:00:00   0  -2  27   16   0   0 2023-01-30 00:00:00  2023-03-02 08:00:00
2023-01-31 00:00:00  2023-02-26 08:00:00   0   0  26    8   0   0 2023-01-31 00:00:00  2023-02-26 08:00:00
2023-01-31 00:00:00  2023-02-27 08:00:00   0   0  27    8   0   0 2023-01-31 00:00:00  2023-02-27 08:00:00
2023-01-31 00:00:00  2023-02-28 08:00:00   0   0  28    8   0   0 2023-01-31 00:00:00  2023-02-28 08:00:00
2023-01-31 00:00:00  2023-03-01 08:00:00   0  -2  29   16   0   0 2023-01-31 00:00:00  2023-03-01 08:00:00
2023-01-31 00:00:00  2023-03-02 08:00:00   0  -2  28   16   0   0 2023-01-31 00:00:00  2023-03-02 08:00:00
2023-02-01 00:00:00  2023-02-27 08:00:00   0   0  26    8   0   0 2023-02-01 00:00:00  2023-02-27 08:00:00
2023-02-01 00:00:00  2023-02-28 08:00:00   0   0  27    8   0   0 2023-02-01 00:00:00  2023-02-28 08:00:00
2023-02-01 00:00:00  2023-03-01 08:00:00   0   1   0    8   0   0 2023-02-01 00:00:00  2023-03-01 08:00:00
2023-02-01 00:00:00  2023-03-02 08:00:00   0   1   1    8   0   0 2023-02-01 00:00:00  2023-03-02 08:00:00
2023-02-02 00:00:00  2023-02-28 08:00:00   0   0  26    8   0   0 2023-02-02 00:00:00  2023-02-28 08:00:00
2023-02-02 00:00:00  2023-03-01 08:00:00   0   0  27    8   0   0 2023-02-02 00:00:00  2023-03-01 08:00:00
2023-02-02 00:00:00  2023-03-02 08:00:00   0   1   0    8   0   0 2023-02-02 00:00:00  2023-03-02 08:00:00
2023-02-03 00:00:00  2023-03-01 08:00:00   0   0  26    8   0   0 2023-02-03 00:00:00  2023-03-01 08:00:00
2023-02-03 00:00:00  2023-03-02 08:00:00   0   0  27    8   0   0 2023-02-03 00:00:00  2023-03-02 08:00:00
2023-02-04 00:00:00  2023-03-02 08:00:00   0   0  26    8   0   0 2023-02-04 00:00:00  2023-03-02 08:00:00 

Sorry for my previous imperfect solutions which I can't delete.

I think the requirement should be:
1) get the difference between two datetime (date or timestamp) values, expressed in years, months, days, hours, minutes and seconds;
2) calendar years and months;
3) non-negative integers, except that seconds can have fractions;
4) maximum values: for months 11, for days 30, for hours 23, for minutes and seconds 59 (plus the fractional seconds);
5) we should be able to calculate one date from the other and the difference ("round trip").

As Tom wrote on August 10, 2005 - 10:34 am UTC, requirements 1, 2 and 4 can be handled by using one "year to month" interval and one "day to second" interval. But he uses ADD_MONTHS, which only works with dates, so we lose any fractional seconds. Also, it sometimes changes the day of the month, which makes round trips impossible.

Anyway, his solution gives wrong answers, such as:

SQL> select
  2    numtoyminterval (months_between(dt2,dt1),'month') years_months,
  3    numtodsinterval(dt2-add_months(dt1, trunc(months_between(dt2,dt1))), 'day') days_hours
  4  from (
  5    select
  6      to_date('2023-02-03 00:00:00','yyyy-mm-dd hh24:mi:ss') dt1,
  7      to_date('2023-04-02 00:00:00','yyyy-mm-dd hh24:mi:ss') dt2
  8    from dual
  9  );

YEARS_ DAYS_HOURS         
------ -------------------
+00-02 +30 00:00:00.000000

Using only "interval expressions" we can accept as input either dates or timestamps without data loss and without changing the day of the month.

To get the difference between February 3d and April 2d, I add 26 days to get March 1st, then 1 month to get April 1st, then 1 day to get April 2d. The total is 1 month (March) and 27 days.
Unfortunately, I can also add 1 month to get to March 3d, then 27 days to get to March 30th! The total is also 1 month (February) and 27 days.

To tell the two apart, I need one additional bit of information: was the starting "day of the month" greater than the ending "day of the month"?

SQL> with data(start_datetime, end_datetime) as (
  2    select date '2023-02-03', timestamp '2023-03-30 00:00:00'
  3    from dual
  4    union all
  5    select date '2023-02-03', timestamp '2023-04-02 00:00:00'
  6    from dual
  7  ), dates as (  
  8    select start_datetime, end_datetime,
  9      trunc(start_datetime,'mm') start_month,
 10      trunc(start_datetime,'mm') + interval '1' month start_next_month,
 11      trunc(end_datetime,'mm') end_month
 12    from data
 13  )
 14  , intervals as (
 15    select start_datetime, end_datetime,
 16      (end_month - start_month) year to month full_iym,
 17      (end_month - start_next_month) year to month next_iym,
 18    (start_datetime - start_month) day to second start_ids,
 19    (start_next_month - start_datetime) day to second start_to_next_ids,
 20    (end_datetime - end_month) day to second end_ids
 21    from dates
 22  )
 23  select start_datetime, end_datetime,
 24    case
 25      when start_ids > end_ids then next_iym
 26      else full_iym
 27    end iym,
 28    case
 29      when start_ids > end_ids then start_to_next_ids + end_ids
 30      else end_ids - start_ids
 31    end ids,
 32    case
 33      when start_ids > end_ids then 1
 34      else 0
 35    end start_gt_end
 36  from intervals;

START_DATETIME      END_DATETIME          IYM    IDS                 START_GT_END
------------------- --------------------- ------ ------------------- ------------
2023-02-03 00:00:00 2023-03-30 00:00:00.0 +00-01 +27 00:00:00.000000            0
2023-02-03 00:00:00 2023-04-02 00:00:00.0 +00-01 +27 00:00:00.000000            1

Knowing whether start_ids was greater than end_ids, I can easily reverse the logic and calculate either date from the other date and the difference.

http://asktom.oracle.com/Misc/DateDiff.html is obsolete, not to mention the occasional slight rounding error that Tom once admitted.

The requirement is to find the difference between two datetime values in days, hours, minutes and seconds (I would add fractional seconds).

For some time, the best solution has simply been
(end_datetime - start_datetime) day to second

SQL> with data(end_datetime, start_datetime) as (
  2    select localtimestamp, current_date - 1.2345
  3  )
  4  select end_datetime, start_datetime,
  5    (end_datetime - start_datetime) day to second datediff 
  6  from data;

END_DATETIME                  START_DATETIME      DATEDIFF           
----------------------------- ------------------- -------------------
2025-01-09 14:28:21,022831000 2025-01-08 08:50:40 +01 05:37:41.022831

We can mix timestamps and dates at will. Also, we can tell from the minus sign which value should come first, without having to double-check the documentation. The result can immediately be used to calculate one datetime from the other datetime and the difference.

If anyone wants to separate days, hours, minutes and seconds then they can use the EXTRACT function.