mysqltimestampdiff

Why do I get a NULL for this timestampdiff()?

Why does the following query in MySQL result in a null for that timestampdiff()?

SELECT EndDate, /* EndDate is YEAR(4) datatype with value 2013 */
       year('2015-01-01') a,
       timestampdiff( YEAR, Year('2015-01-01'), EndDate) b
  FROM table

Results:

 EndDate     a     b
    2013  2015  NULL
Solution

  • MySQL doesn't automatically convert YEAR(4) datatypes to DATETIME. I fixed it by appending a month/day to EndDate and it sees it as a DATETIME.

    SELECT EndDate, timestampdiff(YEAR, STR_TO_DATE('2015-01-01','%Y-%m-%d'), CONCAT(EndDate,'-01-01')) d FROM table

    Thanks to @Uueerdo for identifying the problem.