How can I update just the year only on a date field - i.e. change all records with a year of 2000 to 2006?

Thanks
Kai

Kai <kpeters@vu-ware.com> wrote on Thu, 4 May 2006 23:03:48 -0400

>How can I update just the year only on a date field - i.e. change all records with a year of 2000 to 2006?
>
>Thanks
>Kai
>
Kai


In the last month or so there as been a long thread in one of the Elevate newsgroups about this.  I suggest you do a search - the original question asked about adding 3 months to a date and there are a number of issues - February 29th being just one of them.  You can search the newsgroups from the Elevate website - try "Adding months in SQL" as your search criteria.

Cheers

Jeff


--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Kai,

<< How can I update just the year only on a date field - i.e. change all
records with a year of 2000 to 2006? >>

The leap year issues cause this to be a little more difficult than you
think.   See the thread that Jeff discussed here:

http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=8&msg=11329&page=1#msg11329

--
Tim Young
Elevate Software
www.elevatesoft.com

Ka
> How can I update just the year only on a date field - i.e. change all
> records with a year of 2000 to 2006?

What about

UPDATE ATABLE SET  adate=CAST('2006'+SUBSTRING(CAST(adate AS CHAR(10)) FROM
5 FOR 6) AS DATE)
WHERE EXTRACT(YEAR FROM adate)=2000

John

"John Hay" <jouthay@crbsolutions.co.uk> wrote on Sat, 6 May 2006 08:49:30 +0100

>Ka
>> How can I update just the year only on a date field - i.e. change all
>> records with a year of 2000 to 2006?
>
>What about
>
>UPDATE ATABLE SET adate=CAST('2006'+SUBSTRING(CAST(adate AS CHAR(10)) FROM
>5 FOR 6) AS DATE)
>WHERE EXTRACT(YEAR FROM adate)=2000
>
>John
>
>
John


I think this won't work for 2000-02-29  as 2006 isn't a leap year.  Of course you could fake it with something like:-

UPDATE ATABLE SET adate=CAST('2006'+SUBSTRING(CAST((adate - 1) AS CHAR(10)) FROM
5 FOR 6) AS DATE) + 1
WHERE EXTRACT(YEAR FROM adate)=2000

... I think that works except for 2000-01-01  - so you would have to an "IF(adate= '2000-01-01'..... THEN ... - in which case you might as well have done a test for 2000-02-29 :-\.

It sort of depends if this is a one-off table fix sort of thing or a regular job where the years and increments might change.

Cheers

Jeff



--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz