CODEWITHSUNDEEP
mysqlstr-to-date
sk Jin
sk Jin

Reputation: 33

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?

Upvotes: 0

Views: 251

Answers (2)

Philip Couling
Philip Couling

Reputation: 14883

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');

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

Seems that with a valid year (and then build a complete date) work 

select str_to_date('Fri 12 May 2019', '%a %e %b %Y') ;

Upvotes: 1

Related Questions