Re: datetime .... units operator isn't perfect

Daniel Wright wrote: }To: informix-list@rmy.emory.edu }Cc: }Subject: Re: datetime - datetime = interval but it is always null }Sender: danwright@bigfoot.com }Reply-to: } }Marco Greco wrote: }> }> Yes. If you don't use a multiple of 12 as the number of months (60 obviously }> is a multiple), you are likely to hit err 1267 (the result of a datetime }> computation isout of range) where s_date approaches the end of the month. }> Better use days or years instead. }> }> HTH, }> Marco }> . . . }> Here's a different approach. (The following code works on 4.1) }> }> IF s_date + 60 UNITS MONTH > e_date THEN }> discard... }> ELSE }> place in bin... }> END IF }> . . . }I'm not sure if it defaults to a certain units or not. I do know that }the date format is the number of days since 1970 and you can subtract an }amount of days from that, however, the datetime is a completely }different animal (so I'm told). Apparently, Informix assumed the }correct units on one platform, but the code failed on another. The fix }was to supply the units, which if I recall correctly (I don't really }remember) were supplied as days. }Whether or not the code should have worked on the other platform should }be irrelevant; to write the best possible code is to leave as little to }chance (even if it would be a compiler or database engine problem) as }possible (e.g. Cursors (except hold cursors) are closed automatically . . . }Hope this helps, }- Daniel Wright }danwright@bigfoot.com } It could be platform dependent, what I found out on HP-UX, is that units of days are okay, and perations with units of Month/Year resulting into invalid datetime values: 1996-01-29 + 30 Units Day is 1996-02-28 1996-01-29 + 1 Units Month is 1996-02-29 1996-01-29 + 1 Units Year is 1997-01-29 1996-01-30 + 30 Units Day is 1996-02-29 1996-01-30 + 1 Units Month is null 1996-01-30 + 1 Units Year is 1997-01-30 1996-01-31 + 30 Units Day is 1996-03-01 1996-01-31 + 1 Units Month is null 1996-01-31 + 1 Units Year is 1997-01-31 1996-02-29 + 30 Units Day is 1996-03-30 1996-02-29 + 1 Units Month is 1996-03-29 1996-02-29 + 1 Units Year is null Felix K. Mathews (fmathews@systems.dhl.com) DHL - Systems Inc.