Search code examples
mysqlstr-to-date

str_to_date() does not work on right syntax

I am trying to casting text from type to date type to sort desc.

select str_to_date('FRI 12 MAY', '%a %e %b');

It returns null even though it is correct syntax I think.

Is there any problem on my setting in mysql? or just syntax error?

Solution

  • It really is good to read the manual:

    https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date

    Unspecified date or time parts have a value of 0, so incompletely specified values in str produce a result with some or all parts set to 0:

    But then...

    If the NO_ZERO_DATE or NO_ZERO_IN_DATE SQL mode is enabled, zero dates or part of dates are disallowed. In that case, STR_TO_DATE() returns NULL and generates a warning

    So to fix this, you could either change your settings or add a dummy year:

    select str_to_date(concat('12 MAY', ' 2000'), '%e %b %Y');