Y2K problem on Standard Engine
Carlos, on Informix SE 7.24.UC1 (DBCENTURY=C, DBDATE=DMY4/), got error -1204 "invalid year in date" for the two-digit date "290200" (29 Feb 2000), while 28/02/00 and 29/02/04 worked. Responders explained this as a leap-year/century bug: the engine was treating '00' as 1900 (not a leap year), and noted the 400-year rule makes 2000 a leap year. Art Kagel suggested DBCENTURY wasn't supported in that early 7.24 build, and Madison Pruet identified it as bug 60150, fixed in 7.24UC5 — so the resolution was to upgrade the engine.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We've got a problem with dates in SQL. The next query results in -1204 error (invalid year in date). select ..... where date_field = "290200" In the other hand these querys work fine : select ...... where date_field = "280200" select ...... where date_field = "290204" Does anyone have any idea? Thanks in advance
Bonjour, What's your versions, I don't have this trouble. What's the value of $DBCENTURY ? Carlos <peisa@peisa.com> a 'crit dans le message : 945852410.769472@cache0-serv... > We've got a problem with dates in SQL. > The next query results in -1204 error (invalid year in date). > > select ..... where date_field = "290200" > > In the other hand these querys work fine : > > select ...... where date_field = "280200" > > select ...... where date_field = "290204" > > Does anyone have any idea? > > Thanks in advance > > > >
We've got Informix SE 7.24.UC1, DBCENTURY is set to C and DBDATE to DMY4/, we've been also trying with DBDATE=DMY2/ and it doesn't work either. Christophe MAUMONT <informatique@univitis.fr> escribi' en el mensaje de noticias 83qc8a$l5n$1@jaydee.iway.fr... > Bonjour, > > What's your versions, I don't have this trouble. What's the value of > $DBCENTURY ? > > Carlos <peisa@peisa.com> a 'crit dans le message : > 945852410.769472@cache0-serv... > > We've got a problem with dates in SQL. > > The next query results in -1204 error (invalid year in date). > > > > select ..... where date_field = "290200" > > > > In the other hand these querys work fine : > > > > select ...... where date_field = "280200" > > > > select ...... where date_field = "290204" > > > > Does anyone have any idea? > > > > Thanks in advance > > > > > > > > > >
In article <945852410.769472@cache0-serv>, "Carlos" <peisa@peisa.com> wrote: > We've got a problem with dates in SQL. > The next query results in -1204 error (invalid year in date). > > select ..... where date_field = "290200" > > In the other hand these querys work fine : > > select ...... where date_field = "280200" > > select ...... where date_field = "290204" > > Does anyone have any idea? > > Thanks in advance > > What version of the engine are you running? -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.
I would think that you may have run into an issue where the leap-year is not calculated correctly. "Normally", years that divide by 100 do not have a Feb 29th, e.g. 1900, 1800, 2100, etc. However even though the year 2000 is divisable by 100 it is a millenium and a millenium year DOES have a Feb 29th. Carlos wrote: > We've got a problem with dates in SQL. > The next query results in -1204 error (invalid year in date). > > select ..... where date_field = "290200" > > In the other hand these querys work fine : > > select ...... where date_field = "280200" > > select ...... where date_field = "290204" > > Does anyone have any idea? > > Thanks in advance
I would think that you may have run into an issue where the leap-year is not calculated correctly. "Normally", years that divide by 100 do not have a Feb 29th, e.g. 1900, 1800, 2100, etc. However even though the year 2000 is divisable by 100 it is a millenium and a millenium year DOES have a Feb 29th. Carlos wrote: > We've got a problem with dates in SQL. > The next query results in -1204 error (invalid year in date). > > select ..... where date_field = "290200" > > In the other hand these querys work fine : > > select ...... where date_field = "280200" > > select ...... where date_field = "290204" > > Does anyone have any idea? > > Thanks in advance
I do not think that 7.24UC1 contains DBCENTURY it was added later like to 7.24UC5 or even later. Does the engine accept the date: "29022000"? If so that's the problem it is interpretting the year '00' as '1900'. Looks to me like you have to upgrade. Art S. Kagel Carlos wrote: > > We've got Informix SE 7.24.UC1, DBCENTURY is set to C and DBDATE to DMY4/, > we´ve been also trying with DBDATE=DMY2/ and it doesn´t work either. > > Christophe MAUMONT <informatique@univitis.fr> escribió en el mensaje de > noticias 83qc8a$l5n$1@jaydee.iway.fr... > > Bonjour, > > > > What's your versions, I don't have this trouble. What's the value of > > $DBCENTURY ? > > > > Carlos <peisa@peisa.com> a écrit dans le message : > > 945852410.769472@cache0-serv... > > > We've got a problem with dates in SQL. > > > The next query results in -1204 error (invalid year in date). > > > > > > select ..... where date_field = "290200" > > > > > > In the other hand these querys work fine : > > > > > > select ...... where date_field = "280200" > > > > > > select ...... where date_field = "290204" > > > > > > Does anyone have any idea? > > > > > > Thanks in advance > > > > > > > > > > > > > > > >
This sounds like bug 60150. Fixed in 7.24UC5. Carlos wrote: > We've got a problem with dates in SQL. > The next query results in -1204 error (invalid year in date). > > select ..... where date_field = "290200" > > In the other hand these querys work fine : > > select ...... where date_field = "280200" > > select ...... where date_field = "290204" > > Does anyone have any idea? > > Thanks in advance -- Madison Pruet =========================================== Enterprise Replication Product Developement Dallas, Texas Informix Software ===========================================
Strictly it is not because 2000 is a millenium that it is a leap year but because it is also divisible by 400 so 1600 was a leap year and 2400 will also be a leap year even though they are not millenium years. Art S. Kagel Doug McAllister wrote: > > I would think that you may have run into an issue where the leap-year is > not calculated correctly. > "Normally", years that divide by 100 do not have a Feb 29th, e.g. 1900, > 1800, 2100, etc. However even though the year 2000 is divisable by 100 > it is a millenium and a millenium year DOES have a Feb 29th. > > Carlos wrote: > > > We've got a problem with dates in SQL. > > The next query results in -1204 error (invalid year in date). > > > > select ..... where date_field = "290200" > > > > In the other hand these querys work fine : > > > > select ...... where date_field = "280200" > > > > select ...... where date_field = "290204" > > > > Does anyone have any idea? > > > > Thanks in advance
What version of the engine? What version of the tools? What setting of DBCENTURY (or whoever you spell it?) And does the Informix site say that your versions of Informix are Y2K compliant (including 29/2/2000)? The internal storage is fine (# days since 1/1/1900) but the way in which it is displayed is *not* always fine. I suspect you need a later version of the engine. In article <945852410.769472@cache0-serv>, Carlos <peisa@peisa.com> writes >We've got a problem with dates in SQL. >The next query results in -1204 error (invalid year in date). > >select ..... where date_field = "290200" > >In the other hand these querys work fine : > >select ...... where date_field = "280200" > >select ...... where date_field = "290204" > >Does anyone have any idea? > >Thanks in advance > > > > -- Surfer!
You need Version 7 and to set the DBCENTURY env variable so that 2 digit years are correctly interpreted. Doug McAllister <doug.mcallister@nospam.fmr.com> wrote in message news:CB1575D4D198D311A3F800600803947E541E49@scopent3.mar.hp.com... > I would think that you may have run into an issue where the leap-year is > not calculated correctly. > "Normally", years that divide by 100 do not have a Feb 29th, e.g. 1900, > 1800, 2100, etc. However even though the year 2000 is divisable by 100 > it is a millenium and a millenium year DOES have a Feb 29th. > > Carlos wrote: > > > We've got a problem with dates in SQL. > > The next query results in -1204 error (invalid year in date). > > > > select ..... where date_field = "290200" > > > > In the other hand these querys work fine : > > > > select ...... where date_field = "280200" > > > > select ...... where date_field = "290204" > > > > Does anyone have any idea? > > > > Thanks in advance > >
In article <eDQa4.2843$J9.1896@news.indigo.ie>, Noel Griffin <noel@griffinsoft.com> writes >You need Version 7 and to set the DBCENTURY env variable so that 2 digit >years are correctly interpreted. Not all version 7 engines are correct - some of them (earlier ones) don't know about 29/feb/2000. 7.23 & 7.24 are OK, but I think 7.22 has a 'feature'. > >Doug McAllister <doug.mcallister@nospam.fmr.com> wrote in message >news:CB1575D4D198D311A3F800600803947E541E49@scopent3.mar.hp.com... >> I would think that you may have run into an issue where the leap-year is >> not calculated correctly. >> "Normally", years that divide by 100 do not have a Feb 29th, e.g. 1900, >> 1800, 2100, etc. However even though the year 2000 is divisable by 100 >> it is a millenium and a millenium year DOES have a Feb 29th. >> >> Carlos wrote: >> >> > We've got a problem with dates in SQL. >> > The next query results in -1204 error (invalid year in date). >> > >> > select ..... where date_field = "290200" >> > >> > In the other hand these querys work fine : >> > >> > select ...... where date_field = "280200" >> > >> > select ...... where date_field = "290204" >> > >> > Does anyone have any idea? >> > >> > Thanks in advance >> >> > > -- Surfer!
Doug McAllister wrote: > I would think that you may have run into an issue where the leap-year is > not calculated correctly. > "Normally", years that divide by 100 do not have a Feb 29th, e.g. 1900, > 1800, 2100, etc. However even though the year 2000 is divisable by 100 > it is a millenium and a millenium year DOES have a Feb 29th. > Not to nit-pick, but "millenium years" are not all leap years. The rule is case when year mod 400 = 0 return true when year mod 100 = 0 return false when year mod 4 = 0 return true otherwise return false end case