Skip to main content.
October 1st, 2007

Convert from US Short Date to SQL date using MySQL parsing

I had a customer submit a batch file to be processed in one of our systems and they handed me an Excel file with short dates (mm/dd/yy). Sheesh, this is 2007, can we not get full 4 digit years, please!

so, I wanted to work out a method for quickly converting those date strings within mysql

assume a table of:

UPDATE MyDatesTable
BDMonth = SUBSTRING_INDEX(BirthDate, ‘/’, 1),
BDDay = SUBSTRING_INDEX( SUBSTRING_INDEX(BirthDate, ‘/’, 2 ) , ‘/’, -1 ),
BDYear = SUBSTRING_INDEX(BirthDate, ‘/’, -1)+1900,

UPDATE MyDatesTable
SET BirthDate = CONCAT(BDYear, ‘-’, BDMonth, ‘-’, BDDay)

ALTER TABLEĀ  MyDatesTable MyDatesTable MyDatesTable datetime;

2 digits years are SOOO last century… :-)

Posted by Brian Blood as Data Translation, Database, MySQL, Text Munging at 3:00 PM UTC

No Comments »