Re: datetime conversion help

On Thu, 3 Sep 1998 roger.bruening@METROKC.GOV wrote: > I am trying to do some date time manipulation with SQL (not SPL) so that > where datetimefield > yesterday 2:59 am datetimefield > EXTEND(CURRENT YEAR TO DAY - 1 UNITS DAY, YEAR TO MINUTE) + 2 UNITS HOUR + 59 UNITS MINUTE or datetimefield > EXTEND(CURRENT YEAR TO DAY - 1 UNITS DAY, YEAR TO MINUTE) + INTERVAL(2:59) HOUR TO MINUTE > and datetimefield < today 3am. datetimefield < EXTEND(CURRENT YEAR TO DAY, YEAR TO MINUTE) + 3 UNITS HOUR or datetimefield < EXTEND(CURRENT YEAR TO DAY, YEAR TO MINUTE) + INTERVAL(3:00) HOUR TO MINUTE Note that if datetimefield is a DATETIME YEAR TO SECOND or more precise, this query as written will report on an interval of 24 hours and 1 minute; I would personally write it symmetrically as: datetimefield BETWEEN EXTEND(CURRENT YEAR TO DAY - 1 UNITS DAY, YEAR TO MINUTE) + INTERVAL(3:00) HOUR TO MINUTE AND EXTEND(CURRENT YEAR TO DAY, YEAR TO MINUTE) + INTERVAL(3:00) HOUR TO MINUTE or as: datetimefield >= EXTEND(CURRENT YEAR TO DAY - 1 UNITS DAY, YEAR TO MINUTE) + INTERVAL(3:00) HOUR TO MINUTE AND datetimefield < EXTEND(CURRENT YEAR TO DAY, YEAR TO MINUTE) + INTERVAL(3:00) HOUR TO MINUTE Yes, the calculations make for unwieldy long expressions. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn