Re: Datetime comparison & arithmetic

Easy (not) : select ( extend(a_date, year to second) + (extend(a_time, year to second) - today) ) from ... >>> Christian Brauer <Christian.Brauer@Dresdner-Bank.com> 03/10/00 12:41pm >>> Hello CDI-Members, I don't find any acceptable solution for the following query: table mytable (has lots of data, structure cannot be changed anymore !) a_date DATE, a_time DATETIME HOUR TO SECOND, a_key INTEGER, stuff CHAR(...) I'd like to retrieve some data ordered by point of time (given in following pseudo-code): SELECT Who_Can_Help_To_Combine(a_date, a_time), key FROM mytable ORDER BY 1 The following solutions came to my mind: - use a stored procedure which uses string buffers an datetime conversion - use a temporary table in some way to combine the two date/datetime value Does anyone know an STRAIGHT method to convert a_date,a_time to a_datetime (perhaps using EXTEND,DATETIME,UNITS,MDY....) in my single select ? Any help (except supposing the 2 "solutions" mentioned above) or funny comments (hi Clown) are really welcomed... Read you, Chris (who once thought, that Yesterday and 16:00 would be: 16:00 on Yesterday...)

Richard harnden wrote: > Easy (not) : > > select ( extend(a_date, year to second) > + (extend(a_time, year to second) - today) ) > from ... Does this work? You can only add intervals to DATETIME values. So, the key is to convert the DATETIME HOUR TO SECOND into an INTERVAL HOUR TO SECOND: SELECT extend(a_date, year to second) + (a_time - DATETIME(0) SECOND TO SECOND) The difference between two datetime values is an interval. If you can change the DATETIME column into an interval, life will probably be easier... > >>> Christian Brauer <Christian.Brauer@Dresdner-Bank.com> 03/10/00 12:41pm >>> > I don't find any acceptable solution for the following query: > > table mytable (has lots of data, structure cannot be changed anymore > !) > a_date DATE, > a_time DATETIME HOUR TO SECOND, > a_key INTEGER, > stuff CHAR(...) > > I'd like to retrieve some data ordered by point of time (given in > following > pseudo-code): > SELECT Who_Can_Help_To_Combine(a_date, a_time), key > FROM mytable > ORDER BY 1 > > The following solutions came to my mind: > - use a stored procedure which uses string buffers an datetime > conversion > - use a temporary table in some way to combine the two date/datetime > value > > Does anyone know an STRAIGHT method to convert a_date,a_time to > a_datetime > (perhaps using EXTEND,DATETIME,UNITS,MDY....) in my single select ? > > Any help (except supposing the 2 "solutions" mentioned above) or > funny comments (hi Clown) are really welcomed... -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>

> select ( extend(a_date, year to second) > + (extend(a_time, year to second) - today) ) Thank you for your hints - I missed to subtract 'today' when using EXTEND on Friday. Problem is solved now. Bye folks, Chris

> > select ( extend(a_date, year to second) > > + (extend(a_time, year to second) - today) ) > > from ... > > Does this work? You can only add intervals to DATETIME values. > > The difference between two datetime values is an interval. If you can change the > DATETIME > column into an interval, life will probably be easier... As you pointed out the manual says so, that's why I asked for an EASY solution after studying the online docs. Nevertheless, I tried the statement above and it seems to work in my 4GL application without an error. Bye, Chris