CODEWITHSUNDEEP
mysqltimestampdiff
Johnny Rollerfeet
Johnny Rollerfeet

Reputation: 126

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

Upvotes: 2

Views: 4257

Answers (3)

Johnny Rollerfeet
Johnny Rollerfeet

Reputation: 126

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.

Upvotes: 0

Sasha Pachev
Sasha Pachev

Reputation: 5326

timestampdiff() requires valid dates for the second and third argument. YEAR('2015-01-01') returns 2015, which is not a valid date, which breaks timestampdiff() causing it to return NULL. Just make the second argument '2015-01-01' and as long as your EndDate is good, it should work.

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133370

Try convert the year a stingr to a valid date 

SELECT EndDate, /* EndDate is YEAR(4) datatype with value 2013 */
   STR_TO_DATE('2015-01-01','%Y-%m-%d') a,
   timestampdiff( YEAR,  STR_TO_DATE('2015-01-01','%Y-%m-%d'), EndDate) b
FROM table

Upvotes: 0

Related Questions