MySQL Update Query to Convert Date Formats MM/DD/YYYY to YYYY-MM-DD
I’ll throw this out there for Google to grab in case anybody else needs to do this… I have a MySQL database with a table containing a field called date. In that field (which was poorly setup as a text field) I wanted to change the format of the contents from a MM/DD/YYYY format to YYYY-MM-DD which MySQL and PHP prefer to work with.
The following is an update query which will do the conversion.
UPDATE table SET date = CONCAT(SUBSTRING(date, 7, 4),'-',SUBSTRING(date, 1, 2),'-',SUBSTRING(date, 4, 2))
Be sure you add WHERE or LIMIT clauses if necessary and of course substitute ‘table’ with your table’s name and ‘date’ for the name of your date field.
Explore posts in the same categories: Website Development
February 28th, 2006 at 1:57 pm
This is nice but doesn’t take into account months that might only have one number and dates that might have two. Too bad, I’ve really been digging hard for a way to do this.
February 28th, 2006 at 2:08 pm
No, it doesn’t. It also assumes the date is formatted with slashes.
In my case I already had leading zeroes for both the date and month sections of the field so I didn’t have to worry about it. It could certainly be done with PHP (or any other favorite scripting language) by finding where in the string the slash separators are, then parsing the values between to accommodate for months and dates with or without leading zeros. Then you could also add in the ability to deal with two or four digit years, etc.
May 31st, 2007 at 3:48 pm
Here is a MySQL query that will modify the single digit in DD of MM/DD/YYYY so it has a zero in front of it:
update tablename set datecolumname = replace (datecolumname,’/1/’,'/01/’)
WHERE datecolumname regexp ‘/[0-9]/’;
update tablename set datecolumname = replace (datecolumname,’/2/’,'/02/’)
WHERE datecolumname regexp ‘/[0-9]/’;
update tablename set datecolumname = replace (datecolumname,’/3/’,'/03/’)
WHERE datecolumname regexp ‘/[0-9]/’;
update tablename set datecolumname = replace (datecolumname,’/4/’,'/04/’)
WHERE datecolumname regexp ‘/[0-9]/’;
update tablename set datecolumname = replace (datecolumname,’/5/’,'/05/’)
WHERE datecolumname regexp ‘/[0-9]/’;
update tablename set datecolumname = replace (datecolumname,’/6/’,'/06/’)
WHERE datecolumname regexp ‘/[0-9]/’;
update tablename set datecolumname = replace (datecolumname,’/7/’,'/07/’)
WHERE datecolumname regexp ‘/[0-9]/’;
update tablename set datecolumname = replace (datecolumname,’/8/’,'/08/’)
WHERE datecolumname regexp ‘/[0-9]/’;
update tablename set datecolumname = replace (datecolumname,’/9/’,'/09/’)
WHERE datecolumname regexp ‘/[0-9]/’
This MySQL query will add a zero to the MM portion of MM/DD/YYYY :
update AK2KOrderDetails set ShipDateOD = CONCAT((REPLACE(”", “”,ShipDateOD)), “0″, ShipDateOD )
WHERE ShipDateOD NOT LIKE ‘10%’ AND ShipDateOD NOT LIKE ‘11%’ AND ShipDateOD NOT LIKE ‘12%’
Thanks for the conversion query you provided which does the rest. From a fellow pilot who spent my summers at my parents house on Eustis Ridge. Lots of relatives I have from the Srtatton area. Love Bigelow range.
September 18th, 2007 at 1:01 am
Mark Dyer made this brilliant query and I adjusted it to work
UPDATE tng_people SET birthdatetr=
IF (LOWER(birthdate) regexp
‘(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec){3} [0-9]{4}$’AND
(birthdatetr = “0000-00-00″),
str_to_date(substring_index(birthdate,’ ‘,-3), ‘%e %b %Y’),
if (LOWER(birthdate) regexp
‘(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec){3} [0-9]{4}$’ AND
(birthdatetr = “0000-00-00″),
str_to_date(substring_index(birthdate,’ ‘,-2), ‘%b %Y’),
if(LOWER(birthdate regexp ‘[0-9]{1,2,3,4}$’ AND (birthdatetr = “0000-00-00″),
str_to_date(substring_index(birthdate,’ ‘,-1), ‘%Y’),
birthdatetr
)
)
);
October 11th, 2007 at 4:41 pm
Thanks to everyone who posed these queries, they were a lifesaver!!!!