Convert Date format in SQLite Database

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!

Answer

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