How to change any date format to DATE datatype in MySQL (Insert or Update)

Use this function: STR_TO_DATE('date', 'format')

This accepts two parameters: date and format where date is the input raw date you want to change and format is the corresponding format relative to date that you want to change.

For example: INSERT INTO table(date_field) VALUES(STR_TO_DATE('December 8, 2010','%M %d,%Y'));

Note: table is the name of the table in database and date_field is the name of field in DATE datatype.

The statement above shows 2 parameters date as 'December 8, 2010' and format '%M %d, %Y'. The format is the exact format of the date, so that the mysql knows from what format will it convert the date to DATE datatype format which is '%y-%m-%d'.

Other valid examples are: INSERT INTO table(date_field) VALUES(STR_TO_DATE('12-31-2004', '%m-%d-%Y')); INSERT INTO table(date_field) VALUES(STR_TO_DATE('12/31/2004', '%m/%d/%Y')); UPDATE table SET date_field = STR_TO_DATE('Dec 31, 2004', '%b %d, %Y');