Re: Date Arithmetic in SQL or 4GL

Mark Denham wrote: > For DATE types: > > DEFINE mydate DATE > > LET mydate = TODAY - 10 UNITS DAY -- Substract 10 days > LET mydate = mydate - 100 units MONTH -- You get the idea The calculations there are complex - you push a DATE (encoded as an INTEGER, more or less) and an INTERVAL (encoded as a DECIMAL) onto the I4GL stack, then call dosub() - or it's modern incarnation. It converts the DATE into a DATETIME YEAR TO DAY, subtracts the relevant interval, ad leaves the DATETIME value on the stack; the assignment then converts the DATETIME back to a DATE. Beware the '- 100 UNITS MONTH' usage; it will fail when the start date is 29th or 30th June of a non-leap years, and on 30th June of a leap years (assuming my mental arithmetic is correct). > Using DATE type variables, you can only add, subtract etc in units of YEARS, > MONTHS and DAYS. > > You can do similar stuff to DATETIME values. I have never made much use of > interval values. If memory serves me right, when using interval values the > resulting value is always an interval. It depends on the calculation: DATETIME +/- INTERVAL yields a DATETIME, for example. > From: "Jack A" <InformixMail@yahoo.com> > >>How is date arithmetic done in informix. I've been reading about an >>Interval type but cant find a single example. By date arithmetic I >>mean adding dates and subtracting dates. Which manual would have this >>information. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/