Convert Date format in SQLite Database

Question

I have a SQLite Database and I mistakenly loaded a lot of data with mismatching date formats. There are dates formatted as: MM/DD/YYYY and dates formatted as: DD-Month Abbr. - YY.

I would really like them all to be YYYYMMDD. Is there a way to reformat all of the dates to match the preferred format?

Thanks!

SQLite does not support formatting functions for non ISO dates. Post sample data of your dates that cover all cases. — forpas
Can you explicitly list each and every format that you've used? You can then use where clauses such as LIKE '__/__/____' to find rows of one format and LIKE '__-___-__' to find rows of another format. Then you can write separate translation code for each format, and fix rows of each format, one format at a time. But, first, you need to be explicit about All the formats you need to translate. What you don't want to accidentally do is write a where clause that matches more than one input format, and mangle a row thinking it's formatted one way, but is actually formatted a different way. — MatBailie
Also, your target format should be one supported by Sqlite's functions. That does Not include YYYYMMDD, you should be including the - delimiter... sqlite.org/lang_datefunc.html#time_values — MatBailie

sddk sddk · Accepted Answer · 2021-06-23T18:17:39

If you have only 2 different date formats, you can reformat them by parsing differenly. Follow @MatBailie's suggestion and use a format supported by Sqlite's functions, i used YYYY-MM-DD

I decoded MM/DD/YYYY format by finding parts of a date. And than, i decoded DD-Month Abbr. - YY format separately for each Month.

For MM/DD/YYYY format

update dates set theDate = substr(theDate, 7, 4 ) 
                        || '-' || substr(theDate, 1, 2 ) || '-'
                        || substr(theDate, 4, 2 )
where instr(theDate, '/') > 0;

For DD-Month Abbr. - YY format

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-01-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Jan-') > 0;                         

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-02-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Feb-') > 0;  

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-03-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Mar-') > 0;  

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-04-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Apr-') > 0;  

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-05-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-May-') > 0; 

update dates set theDate = substr(theDate, 9, 4 ) 
                        || '-06-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-June-') > 0; 

update dates set theDate = substr(theDate, 9, 4 ) 
                        || '-07-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-July-') > 0; 

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-08-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Aug-') > 0; 

update dates set theDate = substr(theDate, 9, 4 ) 
                        || '-09-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Sept-') > 0; 

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-10-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Oct-') > 0; 

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-11-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Nov-') > 0; 

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-12-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Dec-') > 0;

Tested on sqlfiddle.com

Convert Date format in SQLite Database

2 Answers