MySQL STR_TO_DATE ends as a "0" (zero)

Question

SELECT STR_TO_DATE('1.1.2000 0:00:00','%e.%c.%Y') will end up like 2000-01-01

but when I'm trying to do the same on column with values 1.1.2000 0:00:00 by running

SELECT 
FirstDisplayedDate,
FirstDisplayedDate = STR_TO_DATE(FirstDisplayedDate,'%e.%c.%Y')
FROM product

I will get zero (NOT NULL!) in every row.

What am I doing wrong? :(

Bud Damyanov · Answer 1 · 2014-10-07T07:56:58.090

The STR_TO_DATE() function is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format.

STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts.

If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning.

The server scans str attempting to match format to it. The format string can contain literal characters and format specifiers beginning with %. Literal characters in format must match literally in str. Format specifiers in format must match a date or time part in str.

And, the reason to get zero is that 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, for example:

mysql> SELECT STR_TO_DATE('abc','abc');
            -> '0000-00-00'
mysql> SELECT STR_TO_DATE('9','%m');
        -> '0000-09-00'
mysql> SELECT STR_TO_DATE('9','%s');
        -> '00:00:09'

Check in your query which format to use for date/time matching and make sure your database rows contains strings, which can be converted as dates and do not assign output values from the function to the same database field:

SELECT 
  FirstDisplayedDate,
  STR_TO_DATE(FirstDisplayedDate,'%e.%c.%Y') AS Converted
FROM product

Here and here is more information how to use the function properly.

score 0 · Answer 2 · answered Oct 07 '14 at 07:52

You get what you write: FirstDisplayedDate = STR_TO_DATE(FirstDisplayedDate,'%e.%c.%Y') compares FirstDisplayedDate with STR_TO_DATE(FirstDisplayedDate,'%e.%c.%Y') and if they are not equal returns zero. I think you want to use alias here so:

SELECT 
  FirstDisplayedDate,
  STR_TO_DATE(FirstDisplayedDate,'%e.%c.%Y') AS FirstDisplayedDate2
FROM product

MySQL STR_TO_DATE ends as a "0" (zero)

2 Answers2