I need to convert dates of varying strings. They come in 3 different ways.
yyyy/mm/dd
mm/dd/yyyy
or blank (can fill in some default)
What is a good way to handle this situation for an INSERT statement?
2 Answers
Use STR_TO_DATE function in combination with COALESCE - something like:
set @strdate := '2014/05/10';
select COALESCE(STR_TO_DATE(@strdate,'%m/%d/%Y'),STR_TO_DATE(@strdate,"%Y/%m/%d"))
1 Comment
O. Jones
Right. sqlfiddle.com/#!9/19168/2 You need to put the format ending in "%Y" first in the
COALESCE() call.You can use MySQL str_to_date() function. It requires one format and will return NULL if the data doesnt match the format. UseCoalesce with str_to_date with str_to_date in descending order of likelihood of the format.
Example if yyyy/mm/dd is more common than mm/dd/yyyy then use
COALESCE(STR_TO_DATE(your_date_here,'%Y/%m/%d'),STR_TO_DATE(@strdate,"%m/%d/%Y"))
Edit: This is the same as Ondřej Šotek's answer but with a possible performance improvement
1 Comment
Ondřej Šotek
You need to use %Y instead of %y, your solution returns wrong year because it works with 2 digit year only.