0

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?

sk Jin
  • 33
  • 5
  • Just your [syntax I think](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date) _If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning_ – RiggsFolly Jan 08 '19 at 16:31
  • Actually that gives me `0000-05-12`, which is not surprising as there is no year on the input – RiggsFolly Jan 08 '19 at 16:37

2 Answers2

2

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');
Philip Couling
  • 13,581
  • 5
  • 53
  • 85
1

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') ;
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107