Hi all,
I am using DB2 ESE V7.. I am trying to find out the difference between two dates.

I have a total of 4 columns.
STRT_DATE (DATE),START_TIME(TIME),
STOP_DATE(DATE),STOP_TIME(TIME)..
START_DATE is always earliar than the STOP_DATE.
How can I find out the time difference in hours and minutes(ex.. 36 hours 23 minutes) between START and STOP.

Thanks in advance

You can either use SQL to get a "time duration" by doing (see the manual on time duration):

timestamp(stop_date,stop_time) - timestamp(start_date,start_time)

or you can write a udf to compute the difference in whatever units you want.

Andy

once u are having 2 timestamps, u can use a
scalar function timestampdiff
to find diff. in seconds, min., days etc.

However look out for the asumptions made for usage of this function.
not sure if it is present in DB2 ESE V7.

--Rahul Singh

Thanks,
I cannot use timestampdiff as this query is running on MVS.
I did use the ARWinner's suggestion to find the time duration by
timestamp(stop_date,stop_time) - timestamp(start_date,start_time).
How do I retreive the duration in minutes from the returned duration value.
Thanks again...

The returned value is a "timestamp difference", to be interpreted as follows:
digits after the decimal point: fraction of seconds (probably to be ignored)
two digits before decimal point: seconds (also ignore if just counting minutes)
two digits before that: minutes (between 00 and 59)
two digits before that: hours (between 00 and 23)
two digits before that: number of days (in a month)
two digits before that: number of months (hopefully equal to 0, otherwise difficult to use because of unequal month lengths ...)
digits before that: number of years (hopefully equal to 0, otherwise difficult to use because of leap years ...)

So you will have to calculate hours + 24 * days if result must be in hours.

Try e.g.where I'm assuming that there's not more than a month difference between the dates.