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

Question

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

My guess would be because `YEAR` is not a date or a datetime data type. https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff — Uueerdo, May 25 '16 at 20:02
Yep. I fixed it by appending a month/day and it sees it as a datetime. `SELECT EndDate, CONCAT(EndDate,'-01-01') a, timestampdiff(YEAR, STR_TO_DATE('2015-01-01','%Y-%m-%d'), CONCAT(EndDate,'-01-01')) d FROM tblboard` — Johnny Rollerfeet, May 26 '16 at 20:41

score 2 · Answer 1 · answered May 25 '16 at 20:04

2

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.

answered May 25 '16 at 20:04

Sasha Pachev

5,162
3
20
20

As @UUeerdo figured out, it didn't convert the YEAR(4) datatype to date. I fixed it by appending a month/day and it sees it as a datetime. ` SELECT EndDate, CONCAT(EndDate,'-01-01') a, timestampdiff(YEAR, STR_TO_DATE('2015-01-01','%Y-%m-%d'), CONCAT(EndDate,'-01-01')) d FROM tblboard` – Johnny Rollerfeet May 26 '16 at 20:44

score 0 · Answer 2 · answered May 25 '16 at 20:02

0

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

answered May 25 '16 at 20:02

ScaisEdge

131,976
10
91
107

score 0 · Accepted Answer · answered May 26 '16 at 20:47

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.

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

3 Answers3