3

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.

ryebread
  • 984
  • 9
  • 30
  • Is running a python/other script on the database an option? – obmarg Oct 17 '13 at 20:40
  • No, unfortunately it is not, since it is an imbedded DB inside a mobile application. I have to make this logic happen when the app launches, so the dates don't get stale even if the app is dormant for several years on someone's device. – ryebread Oct 17 '13 at 20:46
  • Could you just read the dates from the app & then write them back to the db? Or create a custom function to call from the SQL? (http://stackoverflow.com/questions/2108870/how-to-create-custom-functions-in-sqlite). There might be a non-obvious way to do this with a big strftime function, but seems like it'd be less effort to just use non-sql code. – obmarg Oct 17 '13 at 21:00

1 Answers1

6

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.

Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
  • Thank you, sir! You made my day. Not only did it work great, but I was able to learn from your explanation. – ryebread Oct 18 '13 at 14:28