In my table, I have a 'start_date' and 'end_date' column,

eg:

What is the statement that I need to use to extract the years between the start & end date? I want to create & view another column called AS 'num_years_worked' but I'm not sure what to input at the front.

Tried a few variations from Google but couldn't get it to work.

CodePudding user response：

You can just select the YEAR of both dates and build the difference of them:

SELECT start_date, end_date,
YEAR(end_date) - YEAR(start_date) AS difference
FROM yourtable;

You can also use TIMESTAMPDIFF for that:

SELECT start_date, end_date,
TIMESTAMPDIFF(YEAR,start_date,end_date) AS difference
FROM yourtable;

The difference is that this only "counts" entire years, so you might get different results compared to the first option.

Try out which better meets your requirements.

See here a working example according to your sample data: db<>fiddle

CodePudding user response：

you can find it out using this query:

SELECT TIMESTAMPDIFF(YEAR , start_date ,    end_date) from table