Hi,

In Oracle the date subtractions takes the time values also into account and gives the resultant as no.of days

Eg : -

startdate is 2004-07-12-04.00.00 (yyyy-mm-dd-hh.mi:ss)
and enddate is 2004-07-12-06.00.00 (yyyy-mm-dd-hh.mi:ss)

select (a.enddate-a.startdate)*24*60 from test a;

In oracle the duration would be retrieved as 120

In DB2, I used day function for the same as follows :

select (DAY(a.enddate) - DAY(a.startdate)) * 24 * 60 from test a;

It gave zero, since I have taken into account only the days.

I need a single function which can give the no.of days by default considering the hours and converting it into no.of days as Oracle does.

Please advice on the above and give some examples for the functios you suggest.

Thanks,
Sam

I am sorry, in the above post the value 120 retrieved from Oracle is in minutes, how to get the same using DB2 functions

In db2, date and time are stored in the datatype TIMESTAMP ...

By default, in DB2, when you subtract two timestamp values, the output is in yyddmmhhss ... You can use the function TIMESTAMPDIFF to get the difference in terms of seconds, mins etc. Have a look at the SQL Reference

Cheers
Sathyaram