Convert single digit months to double digit

Question

I have a date column which have dates in these formats dd/mm/yyyy, d/m/yyyy.

So basically if the month is January it will have it as 1 instead of 01. But if the month is October it will have it as 10.

How can i convert that column to be in yyyy/mm/dd format?

For example convert 1/1/2021 to 2021/01/01.

Does this answer your question? [SQLLite strftime not reading column value](https://stackoverflow.com/questions/60673844/sqllite-strftime-not-reading-column-value) — DinoCoderSaurus, Feb 12 '21 at 21:04

score 0 · Accepted Answer · answered Feb 12 '21 at 21:13

You can do it with string functions and implicit conversions of strings to integers:

SELECT SUBSTR(datecol, -4) || '/' ||
       SUBSTR('0' || (SUBSTR(datecol, INSTR(datecol, '/') + 1) + 0), -2) || '/' ||
       SUBSTR('0' || (datecol + 0), -2) date
FROM tablename

Change datecol to the name of your column.

If you want to update the column it is better to use the format 'YYYY-MM-DD' which is the only valid text date format for SQLite:

UPDATE tablename
SET datecol = SUBSTR(datecol, -4) || '-' ||
              SUBSTR('0' || (SUBSTR(datecol, INSTR(datecol, '/') + 1) + 0), -2) || '-' ||
              SUBSTR('0' || (datecol + 0), -2)

See a simplified demo.

Convert single digit months to double digit

1 Answers1

Linked