Query question

[buzzt]

I am trying to extract all the dates from a date column in my MySQL database with the year 2003 to update them to 2004 using PHP. Here's what I tried - unsuccessfully - (to extract - haven't gotten to the update part yet):

select * from TABLE where (select extract(year from DATE COLUMN)=2003)

The SELECT EXTRACT(YEAR FROM) is from the MySQL website, but doesn't seem to work this way.

Any ideas?

 

[hos2]

select * from table where year(datefield)=2003

 

[hos2]

and
update tablename
set datefield=adddate(datefield,INTERVAL 1 year)
where year(datefield)=2003

should perhaps do the trick for the update

 

[buzzt]

Thanks!

Any idea about the second query (the update)? I've been playing with this but nothing yet.

update TABLE set year(DATE COLUMN)=year(curdate()) where year(DATE COLUMN)='2003';

 

[hos2]

think you missed my second post

 

[buzzt]

The only problem with doing it that way (which will probably work) is that if there are any years prior, they will not get updated the the current year (2004).

Ultimately, I would like to update all years to the current year without specifying the current year each time if possible, which is why I thought of curdate().

 

[buzzt]

I think we posted at the same time

 

[buzzt]

I still can't get it to set all years to current year. Is it going to be year(curdate()). That does not seem to work.

 

[Luca200]

update TABLE set DATE COLUMN = adddate(DATE COLUMN , interval (year(curdate()) - year(DATE COLUMN)) Year )

 

[buzzt]

Wow! Thanks. This is perfect.