Cleaning up non-ISO Dates in SQLite

SQLite doesn’t have a date storage class which makes working with dates a bit tricky. You’ll have to store the data as ‘Text’. You can read more about that here.

In order to take advantage of date functions within SQLite, your dates need to be stored in an ISO format (‘YYYY-MM-DD’).

The typical issue I run into is working with CSV or Excel data where the default date format is M/D/YYYY. I usually discover the date format after I’ve uploaded the data to a table…

The below code isn’t pretty but I found that using a combination of the SUBSTR (replaces texts) and INSTR (finds the position of the ‘/’ within the dates) will do the trick:

Update 'table_name'
SET  date_column = 
	-- set the year
	substr(date_column,length(date_column)-3,4) ||'-'||
	-- set the month
	CASE WHEN substr(date_column,1,2) = '1/' THEN '01'
	WHEN substr(date_column,1,2) = '2/' THEN '02'
	WHEN substr(date_column,1,2) = '3/' THEN '03'
	WHEN substr(date_column,1,2) = '4/' THEN '04'
	WHEN substr(date_column,1,2) = '5/' THEN '05'
	WHEN substr(date_column,1,2) = '6/' THEN '06'
	WHEN substr(date_column,1,2) = '7/' THEN '07'
	WHEN substr(date_column,1,2) = '8/' THEN '08'
	WHEN substr(date_column,1,2) = '9/' THEN '09'
	WHEN substr(date_column,1,2) = '10' THEN '10'
	WHEN substr(date_column,1,2) = '11' THEN '11'
	WHEN substr(date_column,1,2) = '12' THEN '12' ELSE NULL END ||'-'||
	-- set the day. In my data, the day is only one digit
	CASE WHEN substr(date_column,INSTR(date_column, '/')+1,2) = '1/' then '01'
		WHEN substr(date_column,INSTR(date_column, '/')+1,2) = '2/' then '02'
		WHEN substr(date_column,INSTR(date_column, '/')+1,2) = '3/' then '03'
		WHEN substr(date_column,INSTR(date_column, '/')+1,2) = '4/' then '04'
		WHEN substr(date_column,INSTR(date_column, '/')+1,2) = '5/' then '05'
		WHEN substr(date_column,INSTR(date_column, '/')+1,2) = '6/' then '06'
		WHEN substr(date_column,INSTR(date_column, '/')+1,2) = '7/' then '07'
		WHEN substr(date_column,INSTR(date_column, '/')+1,2) = '8/' then '08'
		WHEN substr(date_column,INSTR(date_column, '/')+1,2) = '9/' then '09'
		ELSE substr(date_column,INSTR(date_column, '/')+1,2) END
WHERE date_column LIKE '%/%';