MySQL STR_TO_DATE() function returns null

ThEpRoGrAmMiNgNoOb picture ThEpRoGrAmMiNgNoOb · Oct 12, 2015 · Viewed 11k times · Source

I wanted to convert my date format From MMMM dd,yyyy to yyyy-MM-dd.

I tried using the following:

SET @dt_to = STR_TO_DATE(dateTo, '%d-%m-%Y');

but returns a NULL value.

How will I convert my date to yyyy-MM-dd format in MySQL?

EDITED:

I am creating a procedure in which the value of dateTo was received in the parameter. It is a date in MMMM dd, yyyy format. E.g. October 10, 2015.

NOTE:

The whole query does not return NULL when I use:

SET @dt_to = dateTo;

Answer

PaulF picture PaulF · Oct 12, 2015

To convert the date format first you need to use STR_TO_DATE to convert the input string to a date value

SET @dt_to = STR_TO_DATE(dateTo, '%M %d,%Y');

and then convert that date value to your required format

SET @dt_converted = DATE_FORMAT(dt_to, '%Y-%m-%d');

or all in 1 go

SET @dt_to = DATE_FORMAT(STR_TO_DATE(dateTo, '%M %d,%Y'), '%Y-%m-%d');