Oninit cdi / Archive
Talk to an expert →

SQL-Error 1263 while calculating date

Posted in 2000

Topics: Stored Procedures & SPL, Platform-Specific Issues

Thomas A. Rohloff — — source: Informix-list mailing list archive (1991-1998)

Hi, if i try select MDY(month(TODAY),1,year(TODAY))- 14 units month as MonthCalc from "informix".dual ; or select MDY(month(TODAY),1,year(TODAY))- interval(15) month to month as MonthCalc from "informix".dual ; i get a SQL-Error 1263: A field in a datetime or interval value is out of range or incorrect.Works well with values below 14. Dual is a table with 1 row. We use 7.31.UC2A on HP-UX. There is no difference between Mode-ANSI and other DBs. This Error also occurs inside a stored procedure. It doesn't satisfy me to rewrite it as select MDY(month(TODAY),1,year(TODAY))- interval(1) year to year - interval(3) month to month as MonthCalc from "informix".dual ; Some ideas?

↪ replying to Thomas A. Rohloff
Jonathan Leffler — — source: Informix-list mailing list archive (1991-1998)

"Thomas A. Rohloff" wrote: > if i try > select MDY(month(TODAY),1,year(TODAY))- 14 units month as MonthCalc from > "informix".dual ; > or > select MDY(month(TODAY),1,year(TODAY))- interval(15) month to month as > MonthCalc from "informix".dual ; > i get a SQL-Error > 1263: A field in a datetime or interval value is out of range or > incorrect.> Works well with values below 14. > Dual is a table with 1 row. > We use 7.31.UC2A on HP-UX. There is no difference between Mode-ANSI and > other DBs. > This Error also occurs inside a stored procedure. > It doesn't satisfy me to rewrite it as > select MDY(month(TODAY),1,year(TODAY))- interval(1) year to year - > interval(3) month to month as MonthCalc from "informix".dual ; > Some ideas? As presented, it sounds like a bug. You could try INTERVAL(15) MONTH(3) TO MONTH; it shouldn't make any difference, but then again, you shouldn't be running into the problem in the first place. By using the 1st of the the month uniformly, you've avoided all the pitfalls of days after the 28th of the month not always having a counterpart in a previous or subsequent month. Does it work with 13? If it blows up from 13 upwards, I'd have half an explanation (and a bug). If 13 works OK, the half explanation really doesn't work. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>

↪ replying to Jonathan Leffler
Jonathan Leffler — — source: Informix-list mailing list archive (1991-1998)

On Mon, 14 Feb 2000, Jonathan Leffler wrote: >"Thomas A. Rohloff" wrote: >> if i try >> select MDY(month(TODAY),1,year(TODAY))- 14 units month as MonthCalc from >> "informix".dual ; >> or >> select MDY(month(TODAY),1,year(TODAY))- interval(15) month to month as >> MonthCalc from "informix".dual ; >> i get a SQL-Error >> 1263: A field in a datetime or interval value is out of range or >> incorrect.>> Works well with values below 14. >> Dual is a table with 1 row. >> We use 7.31.UC2A on HP-UX. There is no difference between Mode-ANSI and >> other DBs. >> This Error also occurs inside a stored procedure. >> It doesn't satisfy me to rewrite it as >> select MDY(month(TODAY),1,year(TODAY))- interval(1) year to year - >> interval(3) month to month as MonthCalc from "informix".dual ; >> Some ideas? > >As presented, it sounds like a bug. As tested under IDS.2000 (9.20.UC1) and CSDK 2.40.UC1 on Solaris 7, the original expression works. If you're really sure it doesn't work on your machine, you have a port-specific or version-specific bug. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v0.95 -- http://www.perl.com/CPAN "Windows is NOT a virus: a virus is small and efficient."

↪ replying to Thomas A. Rohloff
Rudy Fernandes — — source: Informix-list mailing list archive (1991-1998)

Really weird. The same happens on my HPUX10.20 - 7.31UC2 instance. I notice a couple of other oddities. 1. The errors occur when the units of month one subtract is 14 or 15, then again at 26 and 27 and again at 38 and 39 (I checked to 50). Seems to be a pattern here, eh? 2. However if I change the query to the following (ignore the use of systables instead of dual - essentially the same thing). select MDY(month(TODAY),1,year(TODAY)) - 3 units month - $i units month + 3 units month from systables where tabid = 1; Now the errors occur starting at i=23 and 24, then 35 and 36 and 47 and 48. Exceedingly odd! But it does point to a work-around (while you roast Informix tech support for a patch). Use both queries - at least one will always return the correct result. Rudy "Thomas A. Rohloff" wrote: > Hi, > if i try > select MDY(month(TODAY),1,year(TODAY))- 14 units month as MonthCalc from > "informix".dual ; > or > select MDY(month(TODAY),1,year(TODAY))- interval(15) month to month as > MonthCalc from "informix".dual ; > i get a SQL-Error > 1263: A field in a datetime or interval value is out of range or > incorrect.> Works well with values below 14. > Dual is a table with 1 row. > We use 7.31.UC2A on HP-UX. There is no difference between Mode-ANSI and > other DBs. > This Error also occurs inside a stored procedure. > It doesn't satisfy me to rewrite it as > select MDY(month(TODAY),1,year(TODAY))- interval(1) year to year - > interval(3) month to month as MonthCalc from "informix".dual ; > Some ideas?