sqldatetypesmariadbstr-to-date

STR_TO_DATE in mariaDB results in NULL

I have a table with more than 150k entries. The date column contains dates in german format (day.month.year). I can't set 'DATE' as datatype - maria don't accept this format. So I tried to reconfigure the date-string:

select datum,
CAST(STR_TO_DATE(datum, '%d.%M.%Y') AS DATE) 
FROM esf
WHERE ESF_ID = 44421;

Due to the 150k I just tried in one line, avoiding performance issues. Result: Maria finds the related entry - performing the cast results in (NULL). I dont know why?

tried different SQL-calls. Nothing worked

Solution

    1. %M in STR_TO_DATE expects a full month name in current locale, instead use m for a 2 digit month:

    2. STR_TO_DATE returns a date, so there is no need to cast a date to date.

    See also: STR_TO_DATE