SQLite concatenation

Question

UPDATE table 
SET [Date] = SUBSTR([Date], -4) || '-' || (CAST([Date] AS INT)) || '-' ||
             (CAST(SUBSTR([Date], INSTR([Date], '/') + 1) AS INT))

I'm trying to use the above SQLite query to take a date string in the MM/DD/YYYY format and change it to yyyy-mm-dd. It's written the way it is to account for the various ways this particular date format can appear (i.e. M/D/YYYY, M/DD/YYYY and MM/D/YYYY)

If I only take 2 segments of the above query (so the month and year, year and day, or day and month), it works. However, when I try to concatenate the entire query above, something breaks and I just get the original date string.

Any idea what is happening?

Thanks,

Edited to clarify

score 1 · Answer 1 · answered Dec 14 '20 at 17:06

1

You can use string functions like that:

update mytable 
set date = substr(date, -4) || '-' || substr(date, 1, 2) || '-' || substr(date, 4, 2)

answered Dec 14 '20 at 17:06

GMB

216,147
25
84
135

That unfortunately won't work due to how the dates can appear (months and days with only one digit). I've edited my above post to clarify. – HelpWithR Dec 14 '20 at 17:13

Parfait · Answer 2 · 2020-12-14T18:50:41.273

Consider incorporating zero-pad solutions with PRINTR or SUBSTR('00'...) with some nested wrangling for day part:

UPDATE myTable
SET date = SUBSTR(date, -4)   || '-' || 
           PRINTF('%02d', SUBSTR(date, 1, INSTR(date, '/')-1)) || '-' ||  
           PRINTF('%02d', REPLACE(SUBSTR(date, INSTR(date, '/')+1, 2), '/', ''));

UPDATE mytable 
SET date = SUBSTR(date, -4)   || '-' || 
           SUBSTR('00'||SUBSTR(date, 1, INSTR(date, '/')-1), -2) || '-' ||  
           SUBSTR('00'||REPLACE(SUBSTR(date, INSTR(date, '/')+1, 2), '/', ''), -2);

Online Demo

SQLite concatenation

2 Answers2