Oninit cdi / Archive
Talk to an expert →

Is this an informix leap year bug?

Posted in 2000

Topics: Platform-Specific Issues

Thomas B Tatum — — source: Informix-list mailing list archive (1991-1998)

We are using the following on Solaris 2.6: INFORMIX-Universal Server Version 9.14.UC6 ==================== We have the following SQL: select date(today - interval(1) year to year) from systables where tabid = 1; and get 1267: The result of a datetime computation is out of range. Is this a bug with the database? Shouldn't it automatically return 02/28/1999 in this case? Thomas Tatum http://www.koz.com/ 919.767.2129

↪ replying to Thomas B Tatum
Carlson@WHSmith — — source: Informix-list mailing list archive (1991-1998)

Thomas B Tatum wrote: > > We are using the following on Solaris 2.6: > > INFORMIX-Universal Server Version 9.14.UC6 > > ==================== > > We have the following SQL: > > select date(today - interval(1) year to year) from systables where tabid = > 1; > > and get > > 1267: The result of a datetime computation is out of range.> > Is this a bug with the database? Shouldn't it automatically return > 02/28/1999 in this case? > I'm not sure that the bug would be with the database. After all, using interval(4) works OK. It appears that all SQL will do is a quick and dirty year subtraction, and 2000 - 1 = 1999. -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */

↪ replying to Thomas B Tatum
Madison Pruet — — source: Informix-list mailing list archive (1991-1998)

Don't think so. A year ago from today (i.e Feb 29, 1999), did not exist. Try changing it to subtract 256 days instead. This type of problem occurs just about every leap day. The first time I ran into it was when I was a customer many years ago. Thomas B Tatum wrote: > We are using the following on Solaris 2.6: > > INFORMIX-Universal Server Version 9.14.UC6 > > ==================== > > We have the following SQL: > > select date(today - interval(1) year to year) from systables where tabid = > 1; > > and get > > 1267: The result of a datetime computation is out of range.> > Is this a bug with the database? Shouldn't it automatically return > 02/28/1999 in this case? > > Thomas Tatum > http://www.koz.com/ > 919.767.2129 -- Madison Pruet =========================================== Enterprise Replication Product Developement Dallas, Texas Informix Software ===========================================

↪ replying to Thomas B Tatum
Jacob Salomon — — source: Informix-list mailing list archive (1991-1998)

In article <89h2id$5de$1@news.xmission.com>, "Thomas B Tatum" <thomas@koz.com> wrote: > > We are using the following on Solaris 2.6: > > INFORMIX-Universal Server Version 9.14.UC6 > > ==================== > > We have the following SQL: > > select date(today - interval(1) year to year) from systables > where tabid = 1; > > and get > > 1267: The result of a datetime computation is out of range.> > Is this a bug with the database? Shouldn't it automatically return > 02/28/1999 in this case? > > Thomas Tatum > http://www.koz.com/ > 919.767.2129 Tom, of course my response is moot for now, at least until Feb 29, 2004. Here's my understanding of the problem: You are subtracting an interval from a DATE. The engine must convert the date to a DATETIME YEAR TO DAY in order to process the subtraction. Subtracting 1 year from the datetime: 2000-02-29 yields 1999-02-29 . Boioioioing! Bad range. As for the your proposed return value - I claim it should return 03/01/1999 in this case. Who is right? Want an environment variable to determine which way to go on this one? OK, here's how I worked it out yesterday: I conditionally used (Yesterday - 1 year + 1 day). Fortunately in my case, the expression was in a stored procedure so I could use IF/ELSE. If you have 7.3 you can use conditionaly expressions in SQL to get the same result but I do not have 7.3 so I can't try it myself. Simplified SPL code: IF month(current) = 2 AND day(current) = 29 -- ANY leap year THEN let one_year_ago = (today - interval(1) day to day) - (interval(1) year to year) + (interval(1) day to day) ; ELSE let one_year_ago = current - interval(1) year to year ; END IF When I used this on 2000-02-29, it yielded 1999-03-01. Note that it HAD to be conditional; if I unconditionally used that expression the on March 01, then (yesterday - 1 year) would have had the bad range and the above expression would have barfed (or boinged). I'd like to think this helps, but who will remember my contribution 4 years hence? (Boo Hoo.. <:-( ) -- +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.