3

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;
ThEpRoGrAmMiNgNoOb
  • 1,256
  • 3
  • 23
  • 46

3 Answers3

6

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');
PaulF
  • 6,673
  • 2
  • 18
  • 29
3

If it's returning null then that means the extracted datetime value is illegal. You can try like below. See MySQL Documentation for more information.

SELECT STR_TO_DATE('October 10, 2015','%M %d,%Y');
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • 3
    And the definition of "illegal" seems to vary between versions and flavors. e.g. `SELECT str_to_date("4:00PM", "%l:%i%p")` yields `16:00:00` on MariaDB 5.5.5, but `NULL` on MySQL 5.7.18. For latter, you can get away with any year/date: `SELECT str_to_date("1111-11-11 4:00PM", "%Y-%m-%d %l:%i%p")` (and then wrap in 'TIME()' if you just need that). – Max Dec 06 '17 at 22:59
0

In my case, I was getting NULL because I was only supplying a month and year. Some versions of MySQL infer the day (1), but making it explicit fixed this for me.

Westy92
  • 19,087
  • 4
  • 72
  • 54