Update year only in column timestamp date field SQLITE

Question

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));

Tim Biegeleisen · Accepted Answer · 2022-12-23 07:43:41Z

0

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.

answered Dec 23, 2022 at 7:43

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update year only in column timestamp date field SQLITE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related