Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
cancel
Showing results for 
Search instead for 
Did you mean: 

Date field update

Darren Etheridge_2
Super Advisor

Date field update

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.
1 REPLY
Steve Lewis
Honored Contributor

Re: Date field update

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