RE: SQL-Error 1263 while calculating date
To revive a terribly old post:
It's officially a bug now, in case you want to follow it for a fix.
Bug: 126819 - INCORRECT RESULTS RETURNED WHEN SUBTRACTING MONTH UNITS
FROM THE CURRENT DAY AND YEAR
-----Original Message-----
From: Scott Black
Sent: Thursday, February 17, 2000 5:20 PM
To: 'Bernstein, Rick'
Cc: ''Informix-List (E-mail)'; 'William Rice'
Subject: RE: SQL-Error 1263 while calculating date
I guess that about tears it. It must be release specific.
Thanks to all who ran the tests. On to tech support...
-----Original Message-----
From: Bernstein, Rick [mailto:rbernste@alarismed.com]
Sent: Thursday, February 17, 2000 5:15 PM
To: 'William Rice'; Thomas A. Rohloff; Scott Black
Cc: 'Informix-List (E-mail)
Subject: RE: SQL-Error 1263 while calculating date
The queries work for me with IDS 7.30.UC7XK on AIX 4.3.3.
The results were:
mnth yr
11 1998
12 1998
mnth yr
10 1998
11 1998
12 1998
1 1999
1 2000
However, these queries do NOT work with IDS 7.31.UC3-1.
On this Informix version, I receive errors on the first 2 select
statements:
mnth yr
1210: Date could not be converted to month/day/year format
Error in line 3Near character position 31
mnth yr
1210: Date could not be converted to month/day/year format
Error in line 7Near character position 31
Rick Bernstein
ALARIS Medical Systems, Inc.
-----Original Message-----
From: William Rice [mailto:ricew@operamail.com]
Sent: Thursday, February 17, 2000 1:12 PM
To: Thomas A. Rohloff; Scott Black
Cc: 'Informix-List (E-mail)
Subject: RE: SQL-Error 1263 while calculating date
Looks fine for me
AIX 4.3.2
IDS 7.30uc2
My results:
mnth yr
11 1998
12 1998
mnth yr
10 1998
11 1998
12 1998
1 1999
1 2000
>===== Original Message From Scott Black <sblack@elsouth.com> =====
>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?
------------------------------------------------------------
This e-mail has been sent to you courtesy of OperaMail, as a free
service from
Opera Software, makers of the award-winning Web Browser, Opera. Visit
us
at
http://www.opera.com/ or our portal at: http://www.myopera.com/ Your
free
e-mail
account is waiting at: http://www.operamail.com/
------------------------------------------------------------