Re: Possible Feb 29, 2000 Bug? No, this one is not !
Larry Kemmerling wrote:
>
> Informix gurus,
>
> While doing Y2K testing, our organization came across
> a problem trying to subtract a 1 year interval from a
> datetime column with the value "2000-02-29 00:00:00".
> Here's some sample code:
>
> CREATE TEMP TABLE tFoo
> ( DateVal Datetime year to second
> ) WITH NO LOG;>
> INSERT INTO tFoo
> ( DateVal )
> VALUES
> ( "2000-02-29 00:00:00" );>
> SELECT ( DateVal - INTERVAL (365) DAY(9) TO DAY )
> FROM tFoo;
>
> SELECT ( DateVal - INTERVAL (366) DAY(9) TO DAY )
> FROM tFoo;
>
> SELECT ( DateVal - INTERVAL (1) YEAR(9) TO YEAR )
> FROM tFoo;
>
> The last select statement causes error -1267. Error -1267
> says:
>
> -1267 The result of a datetime computation is out of range.
>
Larry,
To me this is not a big surprise, date / date time, add / subtraction,
if not performed on the least significant unit of your data type could
run into above situation. Probably Informix didn't do a good job when
they introduced this date time manipulation feature, but if look at
waht Informix have been doing for quite some time, on these date /
date-time data types, then what you got is not an unexpected result.
Take a look at the following simple example with date fields :
create table t01(dt1 date);
insert into t01 values ("31/07/1998"); -- DBDATE being DMY4/
select * from t01; -- will give 31/07/1998select (dt1 - 31 units day) from t01; -- will give 1998-06-30
select (dt1 - 1 units month) from t01; -- will NOT give 1998-06-30
# ^
# 1267: The result of a datetime computation is out of range.
#
BTW this is not a Feb 29, 2000 bug.
--
Have a nice day
Felix K. Mathews
mailto:fmathews@systems.dhl.com