Date field update

[Darren Etheridge_2]

How would I update the Year in a date field(mm-dd-yyy)in one of my informix tables? I would like to change the year to 2004 if the month is <= 10 and change it to 2005 if the month is > 10.

[Steve Lewis]

Sorry for the non-USA date format(dd/mm/yy), but the code should be the same for you.
The leap year was a bit of a problem.

You will have to amend it for 2002 or other years.

create table x ( f1 date);
insert into x values("27/2/2003");
insert into x values("28/2/2003");
insert into x values("1/3/2003");
insert into x values("30/10/2003");
insert into x values("31/10/2003");
insert into x values("1/11/2003");

select * from x;

update x set f1=(f1+365) where MONTH(f1)<3 and YEAR(f1)=2003;
update x set f1=(f1+366) where MONTH(f1)<=10 and YEAR(f1)=2003;
update x set f1=(f1+731) where MONTH(f1)>10 and YEAR(f1)=2003;

select * from x;

f1

27/02/2003
28/02/2003
01/03/2003
30/10/2003
31/10/2003
01/11/2003
31/12/2003

f1

27/02/2004
28/02/2004
01/03/2004
30/10/2004
31/10/2004
01/11/2005
31/12/2005