Update Date Part Only in Date Time SQL

Question

I have a table that has a ReadingTimeStamp field (DateTime data type). As you can see in the picture. It has 2017-06-19 XX:XX:XX . I want to change only the date part (2017-06-19) to 2017-07-26 without affecting the time. Can someone help me with a query? I am using SQLite Studio. Please see attached file.

UPDATE table1 SET columnDatetime = '10/5/2012' + ' ' + CONVERT(varchar(12), CONVERT(time, columnDatetime)) WHERE CONVERT(date, columnDatetime) = '10/4/2012' — Amul Harad

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2017-07-26T10:10:51

SQLite dates are basically just stored as strings. You can try the following update:

UPDATE yourTable
SET ReadingTimeStamp = '2017-07-26 ' || SUBSTR(ReadingTimeStamp, 12, 8)
WHERE SUBSTR(ReadingTimeStamp, 1, 10) = '2017-06-19'

Update Date Part Only in Date Time SQL

1 Answers