Why does subtracting NOW with DATE column give nonsense numbers?

Question

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...

Which dbms are you using? (DATETIME, NOW and DATE() are all non-standard.) — jarlh, Aug 31 '23 at 14:19
Have a look at [DATEDIFF()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_datediff). A [quick search](https://www.google.com/search?q=mysql+subtract+dates&oq=mysql+subtract+dates) offers plenty of related articles. — user1191247, Aug 31 '23 at 14:25
@user1191247 i think that only works with two destinct dates at a time and not the whole column — samss04, Aug 31 '23 at 14:27
[A little further down the page](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timestampdiff) I previously suggested - `SELECT TIMESTAMPDIFF(YEAR, '2002-08-14', CURRENT_DATE);` — user1191247, Aug 31 '23 at 14:35

score 0 · Answer 1 · answered Sep 01 '23 at 02:58

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

Why does subtracting NOW with DATE column give nonsense numbers?

1 Answers1