Search code examples
sqlmysqldatesql-date-functions

Why does subtracting NOW with DATE column give nonsense numbers?

Picture of Problem

Hello everyone, i have a problem with the subtracting a DATETIME column with todays DATETIME. One of the columns has the HireDate (Date of hiring a certain employee). I want to know how many Years they are employed.

Thank you in advance!

I tried:

SELECT LastName, HireDate, DATE('now'), DATE('now' - HireDate)
FROM employees

or

SELECT LastName, HireDate, DATE('now'), DATE(DATE('now' - HireDate)
FROM employees

I expected: The diffrence, so the time they were hired at the company I got: negative numbers...

Solution

  • DATE(DATE('now' - HireDate) is not valid, nor is it the way to calculate the difference in years. Instead use timestampdiff()

    SELECT
  LastName
, HireDate
, TIMESTAMPDIFF(year, HireDate, now() )
FROM employees

    See it working at sqlfiddle

    Similar question: https://dba.stackexchange.com/questions/112558/mysql-how-to-make-a-datediff-in-years