Cute issue
Date arithmetic like "today - 18 UNITS MONTH" can produce an invalid date (e.g. Feb 30) and fail with error -1267, which happens whenever the month shift lands on a non-existent day (31st of a short month, Feb 29/30/31), for additions as well as subtractions. Posters explained this is long-standing, standards-conformant behaviour: the engine won't guess whether to round to the previous or next valid day. Workarounds offered: subtract in UNITS DAY (e.g. 547/550 days) or use the ADD_MONTHS() function, which has defined rounding behaviour.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Just in case somebody else runs into this today. Try this: select today - 18 units month from systables where tabid=3D1. It "would" yield 2/30/2010 - which is an illegal date and kicks out a = -1267 error. If you change to use "550 units day" it works fine (or 547 if you want = to be more "precise"). This will happen 7 times a year (well 6 if you allow for leap years). = 3/31, 5/31, 8/29, 8/30, 8/31, 10/31, 12/31. j.=
This is a well known issue... AFAIK we are ANSI compatible on this, but putting it in another way, the idea is that we (the Informix engine) will not take decisions that pertain to the user (which day would it choose if the date is invalid? The previous or the next?) The function ADD_MONTHS() introduced in 11.5 (or 11.1, not really sure) will solve this dilemma as is has a very well known behavior. This is one of the situations where the Informix behavior is formally correct, but that tends to annoy users. In any case, when I ask the question "which day would we choose?" they usually understand the issue. Other databases assume things (as the function above). Regards. On Tue, Aug 30, 2011 at 3:48 PM, Jack Parker <jack.parker4@verizon.net>wrote: > Just in case somebody else runs into this today. > > Try this: > > select today - 18 units month from systables where tabid=3D1. > > It "would" yield 2/30/2010 - which is an illegal date and kicks out a = > -1267 error. > > If you change to use "550 units day" it works fine (or 547 if you want = > to be more "precise"). > > This will happen 7 times a year (well 6 if you allow for leap years). = > 3/31, 5/31, 8/29, 8/30, 8/31, 10/31, 12/31. > > j.= > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001517592c5a4c1e5504abba80cb
Back in the Y2K testing days I notice the OS handles 'bad' dates differently, AFAIR if you moved the Unix date to an 'illegal' date with AIX then it failed and the date was not changed, but with Solaris you were moved to the next 'legal' date Cheers Paul -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Tuesday, August 30, 2011 10:17 AM To: ids@iiug.org Subject: Re: Cute issue [24768] This is a well known issue... AFAIK we are ANSI compatible on this, but putting it in another way, the idea is that we (the Informix engine) will not take decisions that pertain to the user (which day would it choose if the date is invalid? The previous or the next?) The function ADD_MONTHS() introduced in 11.5 (or 11.1, not really sure) will solve this dilemma as is has a very well known behavior. This is one of the situations where the Informix behavior is formally correct, but that tends to annoy users. In any case, when I ask the question "which day would we choose?" they usually understand the issue. Other databases assume things (as the function above). Regards. On Tue, Aug 30, 2011 at 3:48 PM, Jack Parker <jack.parker4@verizon.net>wrote: > Just in case somebody else runs into this today. > > Try this: > > select today - 18 units month from systables where tabid=3D1. > > It "would" yield 2/30/2010 - which is an illegal date and kicks out a = > -1267 error. > > If you change to use "550 units day" it works fine (or 547 if you want = > to be more "precise"). > > This will happen 7 times a year (well 6 if you allow for leap years). = > 3/31, 5/31, 8/29, 8/30, 8/31, 10/31, 12/31. > > j.= > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001517592c5a4c1e5504abba80cb **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. _____ avast! Antivirus <http://www.avast.com>; : Outbound message clean. Virus Database (VPS): 110830-1, 08/30/2011 Tested on: 8/30/2011 10:32:25 AM avast! - copyright (c) 1988-2011 ALWIL Software.
Interesting... I believe any behavior is acceptable if we know it... Another aspect of Informix that reminds me of this one is the null behavior. We're very strict with NULL handling... A concatenation with NULL returns NULL. This is correct, but people tend to consider it annoying. Another one is the way we handle "CURRENT" inside statements in general and procedures in particular. We're ANSI compliant but again most of the times people find it annoying... Regards. On Tue, Aug 30, 2011 at 4:32 PM, Paul Watson <paul@oninit.com> wrote: > Back in the Y2K testing days I notice the OS handles 'bad' dates > differently, AFAIR if you moved the Unix date to an 'illegal' date with AIX > then it failed and the date was not changed, but with Solaris you were > moved > to the next 'legal' date > > Cheers > Paul > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando Nunes > Sent: Tuesday, August 30, 2011 10:17 AM > To: ids@iiug.org > Subject: Re: Cute issue [24768] > > This is a well known issue... AFAIK we are ANSI compatible on this, but > putting it in another way, the idea is that we (the Informix engine) will > not take decisions that pertain to the user (which day would it choose if > the date is invalid? The previous or the next?) > > The function ADD_MONTHS() introduced in 11.5 (or 11.1, not really sure) > will > solve this dilemma as is has a very well known behavior. > > This is one of the situations where the Informix behavior is formally > correct, but that tends to annoy users. In any case, when I ask the > question > "which day would we choose?" they usually understand the issue. Other > databases assume things (as the function above). > Regards. > > On Tue, Aug 30, 2011 at 3:48 PM, Jack Parker > <jack.parker4@verizon.net>wrote: > > > Just in case somebody else runs into this today. > > > > Try this: > > > > select today - 18 units month from systables where tabid=3D1. > > > > It "would" yield 2/30/2010 - which is an illegal date and kicks out a = > > -1267 error. > > > > If you change to use "550 units day" it works fine (or 547 if you want = > > to be more "precise"). > > > > This will happen 7 times a year (well 6 if you allow for leap years). = > > 3/31, 5/31, 8/29, 8/30, 8/31, 10/31, 12/31. > > > > j.= > > > > > > > > > > **************************************************************************** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001517592c5a4c1e5504abba80cb > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > _____ > > avast! Antivirus <http://www.avast.com>; : Outbound message clean. > > Virus Database (VPS): 110830-1, 08/30/2011 > Tested on: 8/30/2011 10:32:25 AM > avast! - copyright (c) 1988-2011 ALWIL Software. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --002215b0346659d0f504abbb1913
On Tue, Aug 30, 2011 at 07:48, Jack Parker <jack.parker4@verizon.net> wrote: > Just in case somebody else runs into this today. > > Try this: > > select today - 18 units month from systables where tabid=3D1. > > It "would" yield 2/30/2010 - which is an illegal date and kicks out a = > -1267 error. > > If you change to use "550 units day" it works fine (or 547 if you want = > to be more "precise"). > > This will happen 7 times a year (well 6 if you allow for leap years). = > 3/31, 5/31, 8/29, 8/30, 8/31, 10/31, 12/31. > It can happen more often than that. It can happen on the 31st of any month when the subtraction would land you on the 31st of February, April, June, September or November. It can happen on the 30th of any month when the subtraction would land you on 30th February. It can happen on the 29th of any month when the subtraction would land you on the 29th February of a non-leap year. It can also happen with additions. It is 'well-known' behaviour - that is, it has behaved this way since 1990 when DATETIME and INTERVAL types were introduced. I have railed about it before on comp.databases.informix (aka informix-list@iiug.org). -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --90e6ba1eff449af41404abbb209c
I do not question the behaviour. As soon as someone asked me what the = problem was I knew immediately. I merely point out that today is one of = those special days where this will fail and left a breadcrumb for the = newbie who is puzzled. The actual days it will fail will of course vary with the interval you = are using, but with the 18 month example, there are two 31 day months = where it actually works July (to january) and January (to july).=20 Thank you all for chiming in. j. On Aug 30, 2011, at 12:01 PM, Jonathan Leffler wrote: > On Tue, Aug 30, 2011 at 07:48, Jack Parker <jack.parker4@verizon.net> = wrote:=20 >=20 >> Just in case somebody else runs into this today.=20 >>=20 >> Try this:=20 >>=20 >> select today - 18 units month from systables where tabid=3D3D1.=20 >>=20 >> It "would" yield 2/30/2010 - which is an illegal date and kicks out a = =3D=20 >> -1267 error.=20 >>=20 >> If you change to use "550 units day" it works fine (or 547 if you = want =3D=20 >> to be more "precise").=20 >>=20 >> This will happen 7 times a year (well 6 if you allow for leap years). = =3D=20 >> 3/31, 5/31, 8/29, 8/30, 8/31, 10/31, 12/31.=20 >>=20 >=20 > It can happen more often than that. It can happen on the 31st of any = month=20 > when the subtraction would land you on the 31st of February, April, = June,=20 > September or November. It can happen on the 30th of any month when the=20= > subtraction would land you on 30th February. It can happen on the 29th = of=20 > any month when the subtraction would land you on the 29th February of = a=20 > non-leap year. It can also happen with additions.=20 >=20 > It is 'well-known' behaviour - that is, it has behaved this way since = 1990=20 > when DATETIME and INTERVAL types were introduced.=20 >=20 > I have railed about it before on comp.databases.informix (aka=20 > informix-list@iiug.org).=20 >=20 > --=20 > Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>=20= > Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org=20 > "Blessed are we who can laugh at ourselves, for we shall never cease = to be=20 > amused."=20 >=20 > --90e6ba1eff449af41404abbb209c=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20