Apologize if this has been answered, but I can't find it:

Suppose you have an entire column in MySQL that is YYYY-MM-DD, and you want to select it as the number of Months to a specific date, and also do it as a new column. Example date counting to can be March 2020.

So the following (table_1):

To

But do this for every date in the Hire_Date column without manually entering the start and end date in DATEDIFF or TIMESTAMPDIFF.

I have the following, which returns a new column, but with NULL values:

select Emp_Name, timestampdiff(month, 2021-04-01, Hire_Date) as Months_Employed from table_1

I have also tried DATEDIFF and dividing by 12 but it doesn't work.

Any help is appreciated.

CodePudding user response：

The DATEDIFF function returns the difference in number of days. If you want the number of months, you need to divide this number by 30 (assuming that a month has 30 days on average).

Since your output is NULL, maybe there's an issue with the Hire_Date type, hence I would CAST the value of Hire_Date to the DATE type.

SELECT 
    Emp_Name, 
    DATEDIFF(
        CAST('2021-04-01' AS DATE), 
        CAST(Hire_Date    AS DATE)
    )/30         AS Months_Employed 
FROM 
    table_1

Does it work for you?

CodePudding user response：

TIMESTAMPDIFF(MONTH, startDate, endDate) is the correct function to use.

Date constants need to be shown as text strings. '2021-04-01' is April Fools Day 2021. But 2021-04-01, without the quotes, is an arithmetic expression evaluating to 2016.

Here's a fiddle.

SELECT emp_name, 
       hire_date, 
       TIMESTAMPDIFF(MONTH, hire_date, '2022-03-07') 
  FROM table_1