Update only the year in SQLite column

Question

I have a SQLite3 database that has 366 rows in it, and a date for each day of the year. I need to make sure that the year is current, and if it is not, then I need to update the year to the current calendar year. What I would like to do is something like the following:

UPDATE daily SET date = DATE('%Y', 'now');

or

UPDATE daily SET date = strftime('%Y', 'now');

But those queries just make the date column NULL, and even if they did work as I expected them to, I doubt that it would retain the day and month that is there already.

In MySQL, I can do something like UPDATE daily SET date = ADDDATE(date, INTERVAL 1 YEAR) -- but firstly, it is not a valid SQLite query, and secondly, I need to update to the current year, not just step up one year.

Any help would be appreciated.

Answer 1

Try this:

create table t (id int, d text);

insert into t
select 1, date('2011-01-01') union
select 2, date('2012-03-11') union
select 3, date('2013-05-21') union
select 4, date('2014-07-01') union
select 5, date('2015-11-11');

select * from t;

update t set
    d = date(strftime('%Y', date('now')) || strftime('-%m-%d', d));

select * from t;

It uses Date And Time Functions. Firstly it takes month and day from field (strftime('-%m-%d', d)) then add (concatenate) current year (strftime('%Y', date('now'))) and converts it to date.

SQL Fiddle live example.