Press "Enter" to skip to content

Category: Data Translation

moving data from one format/system to another.

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:

  • BirthDate char(16)
  • BDMonth tinyint
  • BDDay tinyint
  • BDYear smallint

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… 🙂