Search code examples
sqlitedb-browser-sqlite

Update year only in column timestamp date field SQLITE

I want to update the year only to 2025 without changing the month day and time

what I have

2027-01-01 09:30:00
2012-03-06 12:00:00
2014-01-01 17:24:00
2020-07-03 04:30:00
2020-01-01 05:50:00
2021-09-03 06:30:00
2013-01-01 23:30:00
2026-01-01 08:30:00
2028-01-01 09:30:00

what i required is below:

2025-01-01 09:30:00
2025-03-06 12:30:00
2025-01-01 17:24:00
2025-07-03 04:30:00
2025-01-01 05:50:00
2025-09-03 06:30:00
2025-01-01 23:30:00
2025-01-01 08:30:00
2025-01-01 09:30:00
I am using dB Browser for SQLite

what i have tried but it didn't worked update t set d = datetime(strftime('%Y', datetime(2059)) || strftime('-%m-%d', d));

Solution

  • You may update via a substring operation:

    UPDATE yourTable
SET ts = '2025-' || SUBSTR(ts, 6, 14);

    Note that SQLite does not actually have a timestamp/datetime type. Instead, these values would be stored as text, and hence we can do a substring operation on them.