Monthly Archives: October 2016

Formatting DATE and DATETIME in Mysql

Did you ever inherit a table with a VARCHAR for one of the date fields?
Doesn’t seem that bad, except that gives license for people to start putting different date formats into it.

e.g.
2001-May-05 11:30
11-19-2009 23:33
Nov 4, 1998 8:03
3/18/08 3:50
8-15-1999 13:00

You should put these into a DATE or DATETIME column. And here’s how you would parse them

SELECT id, strDate,
  CASE WHEN LENGTH(DATE(STR_TO_DATE(strDate,"%Y-%m-%d %H:%i:%S"))) IS NOT NULL THEN STR_TO_DATE(strDate,"%Y-%m-%d %H:%i:%S")
       WHEN LENGTH(DATE(STR_TO_DATE(strDate,"%Y-%M-%d %H:%i:%S"))) IS NOT NULL THEN STR_TO_DATE(strDate,"%Y-%M-%d %H:%i:%S")
       WHEN LENGTH(DATE(STR_TO_DATE(strDate,"%d-%M-%Y %H:%i:%S"))) IS NOT NULL THEN STR_TO_DATE(strDate,"%d-%M-%Y %H:%i:%S")
  END AS newDate
FROM date_table
WHERE strDate IS NOT NULL

Add as many formats as you like and make sure you test!

Also, if you wanted to update the date_table with this new DATETIME value, you can do this

UPDATE date_table
SET newDate = CASE
  WHEN LENGTH(DATE(STR_TO_DATE(strDate,"%Y-%m-%d %H:%i:%S"))) IS NOT NULL THEN STR_TO_DATE(strDate,"%Y-%m-%d %H:%i:%S")
  WHEN LENGTH(DATE(STR_TO_DATE(strDate,"%Y-%M-%d %H:%i:%S"))) IS NOT NULL THEN STR_TO_DATE(strDate,"%Y-%M-%d %H:%i:%S")
  WHEN LENGTH(DATE(STR_TO_DATE(strDate,"%d-%M-%Y %H:%i:%S"))) IS NOT NULL THEN STR_TO_DATE(strDate,"%d-%M-%Y %H:%i:%S")  
END
WHERE strDate IS NOT NULL
AND newDate IS NULL

One thing to note. If you’re going to CREATE, UPDATE or INSERT into a table with these values, there’s a chance you may run into the following error

 “Incorrect datetime value: ‘XXXX’ for function str_to_date”

It may be that your MySql server is running in strict mode.

To check, run

select @@session.sql_mode

It might produce something like “STRICT_ALL_TABLES” or “STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION”

To set it to a less strict mode, run

set session sql_mode =''

Now your UPDATE, INSERT or CREATE should work.

Once it completes, you may want to set the sql_mode back to the previous value.

Advertisements
Tagged