How to change Text Date into Date data type in SQLite and change its display format

Question

I have a column that its contents are dates as text (For example, "5/24/2023"). There are more than 213000 records. How can I write a query to change the date to "2023-05-24" format? I studied this post but it couldn't solve my problem.

Answer 1

There is no Date data type in SQLite.

Your dates are strings (TEXT data type) and you can use string functions to convert their format to the proper ISO format:

UPDATE tablename
SET date = SUBSTR(date, -4) || '-' ||
           printf('%02d', date + 0) || '-' ||
           printf('%02d', SUBSTR(date, INSTR(date, '/') + 1) + 0);

Change date to the name of your column.

See the demo.