RE: SQL-Error 1263 while calculating date

Posted in 2000

Scott Black — Thu, 17 Feb 2000 13:31:16 -0500 — source: Informix-list mailing list archive (1991-1998)

Thomas; Just to add my two cents, I get the same error. I'm using HP-UX 10.20, Informix 7.31.UC5. I wonder if you (or anyone else who might be interested) would try the following selects along a similar vein: select month(today - 15 units month) mnth, year(today -15 units month) yr from systables where tabid = 1 union all select month(today - 14 units month) mnth, year(today -14 units month) yr from systables where tabid = 1 ; select month(today - 16 units month) mnth, year(today -16 units month) yr from systables where tabid = 1 union all select month(today - 15 units month) mnth, year(today -15 units month) yr from systables where tabid = 1 union all select month(today - 14 units month) mnth, year(today -14 units month) yr from systables where tabid = 1 union all select month(today - 13 units month) mnth, year(today -13 units month) yr from systables where tabid = 1 union all select month(today - 1 units month) mnth, year(today - 1 units month) yr from systables where tabid = 1 Please notice that both calculate the month and year from 15, and 14 months ago. On my system the first select returns December 1899 for both parts of the union. That's odd enough, but the second select returns October 1998, and January 1900! Is anyone else experiencing this same behavior? TIA -----Original Message----- From: Thomas A. Rohloff [mailto:rohloff@sozialkasse-berlin.de] Sent: Monday, February 14, 2000 10:03 AM Posted To: informix Conversation: SQL-Error 1263 while calculating date Subject: SQL-Error 1263 while calculating date 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?