How to update DATETIME field with existing data from same table

Question

I have an sqlite database which currently holds an integer field called Year which currently only stores the year. In future versions I want to store a full date and time.

I updated my table to include a FullDate field using alter table.

> ALTER TABLE Files ADD COLUMN UploadDate DATETIME DEFAULT 0;

Next, I want to migrate all the existing year fields to the new field. So I'm looking for something like:

> UPDATE Files SET UploadDate = (DATETIME('%Y-%m-%d', Year, 1, 1));

Unfortunately this doesn't seem to work as the result is empty. I also tried the date and strftime functions but they either result in incorrect data or empty data.

What's the proper way to update a DATETIME field with existing data in the same table?

Answer 1

The DATE and DATETIME functions don't have a format parameter.

For more: http://sqlite.org/lang_datefunc.html

The main catch is that SQLite does not have any date or time types, so that you might as well populate your field with:

UPDATE Files SET UploadDate = Year || '-01-01';

And that will do the exact same thing. Dates are not stored as typed, but can be evaluated as such against the date and time functions.