STR_TO_DATE in mariaDB results in NULL

Question

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

Answer 1

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

Answer 2

Check the date:

SELECt *
FROM (
   SELECT
      LEFT(datum,2) as D,
      MID(datum,4,2) as M,
      RIGHT(RTRIM(datum),4) as Y
   FROM esf
  ) x
WHERE d<1 or d>31 or m<1 or m>12 or y<1970 or y>2023;

DBFIDDLE needs some improvement for month with less than 31 days! (and of course the range in the year should be adapted to your own needs)