Subtracting 2 datetime values

Posted in 2009

GLEN WENZEL — Tue, 31 Mar 2009 01:16:57 — source: IIUG Forums & Mailing Lists

Hi, I am an SQL rookie and am hoping someone can help with a general Informix SQL question. I have 2 datetime values (each is DATETIME YEAR TO MINUTE), and I would like to subtract them and get the difference expressed as a number of hours (hopefully to 1 decimal place). I have been unable to figure out a way to do this. So, for example: say the fields are "end_dt" and "start_dt" , and they have these values: 2009-03-17 16:30 and 2009-03-17 10:00 I would like to subtract them with a result of 6.5 hours: 2009-03-17 16:30 minus 2009-03-17 10:00 = 6.5 hours I have tried a number of ways, but am stumped as to how this could be done. Any suggestions would be very welcome. Thanks very much ! Glen

↪ replying to GLEN WENZEL

Jarrod Teale — Tue, 31 Mar 2009 02:06:14 — source: IIUG Forums & Mailing Lists

create table dt1 ( col1 datetime year to minute, col2 datetime year to minute); insert into dt1 values ('2009-03-17 16:30', '2009-03-17 10:00'); select (col1 - col2)::INTERVAL MINUTE(8) TO MINUTE::CHAR(9)::DECIMAL / 60 as hours_different from dt1 Returns 6.5 It will fail if the two dates are more than 99999999 minutes apart (190 years) Jarrod Teale Fonterra NZ -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of GLEN WENZEL Sent: Tuesday, 31 March 2009 2:17 p.m. To: ids@iiug.org Subject: Subtracting 2 datetime values [15372] Hi, I am an SQL rookie and am hoping someone can help with a general Informix SQL question. I have 2 datetime values (each is DATETIME YEAR TO MINUTE), and I would like to subtract them and get the difference expressed as a number of hours (hopefully to 1 decimal place). I have been unable to figure out a way to do this. So, for example: say the fields are "end_dt" and "start_dt" , and they have these values: 2009-03-17 16:30 and 2009-03-17 10:00 I would like to subtract them with a result of 6.5 hours: 2009-03-17 16:30 minus 2009-03-17 10:00 = 6.5 hours I have tried a number of ways, but am stumped as to how this could be done. Any suggestions would be very welcome. Thanks very much ! Glen ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. DISCLAIMER: This email contains confidential information and may be legally privileged. If you are not the intended recipient or have received this email in error, please notify the sender immediately and destroy this email. You may not use, disclose or copy this email or its attachments in any way. Any opinions expressed in this email are those of the author and are not necessarily those of the Fonterra Co-operative Group. http://www.fonterra.com/

↪ replying to GLEN WENZEL

PING TANG — Tue, 31 Mar 2009 15:59:37 — source: IIUG Forums & Mailing Lists

Hi Glen Do you mean you want to display 6.5 instead of 6:30? To display 6:30 char out_str[16] EXEC SQL BEGIN DECLARE SECTION; interval hour to minute intv; EXEC SQL END DECLARE SECTION; dtcvasc("2009-03-17 16:30", &end_dt); dtcvasc("2009-03-17 10:00", &start_dt); dtsub(&end_dt,&start_dt, &intv); dttoasc(&final1, out_str); ===================== result: <06:30> Hours To display 6.50 char out_str[16] EXEC SQL BEGIN DECLARE SECTION; interval minute(5) to minute intv; EXEC SQL END DECLARE SECTION; dtcvasc("2009-03-17 16:30", &end_dt); dtcvasc("2009-03-17 10:00", &start_dt); dtsub(&end_dt,&start_dt, &intv); dttoasc(&final1, out_str); ========================== result: <0390> minutes Then you can convert it to 6.5 --- On Mon, 3/30/09, GLEN WENZEL <glen_wenzel@cpr.ca> wrote: > From: GLEN WENZEL <glen_wenzel@cpr.ca> > Subject: Subtracting 2 datetime values [15372] > To: ids@iiug.org > Date: Monday, March 30, 2009, 8:16 PM > Hi, > > I am an SQL rookie and am hoping someone can help with a > general Informix SQL > question. > > I have 2 datetime values (each is DATETIME YEAR TO MINUTE), > and I would like > to subtract them and get the difference expressed as a > number of hours > (hopefully to 1 decimal place). I have been unable to > figure out a way to do > this. > > So, for example: say the fields are "end_dt" and "start_dt" > , and they have > these values: 2009-03-17 16:30 and 2009-03-17 10:00 > > I would like to subtract them with a result of 6.5 hours: > > 2009-03-17 16:30 minus 2009-03-17 10:00 = 6.5 hours > > I have tried a number of ways, but am stumped as to how > this could be done. > Any > suggestions would be very welcome. Thanks very much ! > > Glen > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the > discussion forum. > >

↪ replying to PING TANG

GLEN WENZEL — Tue, 31 Mar 2009 16:09:59 — source: IIUG Forums & Mailing Lists

Jarrod, Ping, Douglas, Thank you all very much for your excellent suggestions. Much appreciated ! Glen