Re: SQL HELP
On Thu, 28 Jan 1999, Umesh Patel wrote:
> How do I convert datetime datatype to hours.
> I would like to find out diff. between two datetime values in hours.
> e.g.
> select current - datetime from tbl xx
Hmmm; so you want the interval between to datetime values expressed in
hours. Do you care about fractional hours? If so, you're going to have to
do some work on the code below, like changing the types of the arguments
and using a SECOND(9) to SECOND interval, dividing by 3600, and worrying
about intervals too big to fit into a SECOND(9) to SECOND interval.
This is yet another minor variant one of the most FA of FAQs, with yet
another minor variant on one of the most frequently supplied of FSAs:
CREATE PROCEDURE difftime_hours(d1 DATETIME YEAR TO HOUR,
d2 DATETIME YEAR TO HOUR DEFAULT CURRENT YEAR TO HOUR)
RETURNING INTEGER; DEFINE i INTERVAL HOUR(9) TO HOUR;
DEFINE s CHAR(10);
LET i = d1 - d2;
LET s = i;
RETURN s;
END PROCEDURE;
EXECUTE PROCEDURE difftime_hours(CURRENT);0
EXECUTE PROCEDURE difftime_hours(CURRENT + 1 UNITS HOUR);1
EXECUTE PROCEDURE difftime_hours(CURRENT, DATETIME(1970-01-01 00) YEAR TO HOUR);254867
Yours,
Jonathan Leffler (jleffler@informix.com) #include <wish/I/was/skiing.h>
Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN
Informix IDN for D4GL & Linux -- http://www.informix.com/idn