# Difference between two dates in Years, Months and days

• ### Question

• User1623409651 posted

How to get total service ( years , Months and days)  from joining date to resigned date for an employee using  dateDiff function in single select statement .

I have tried this

`SELECT DATEDIFF(year, JoingDate, ResignedDate) + '' + DATEDIFF(MONTH, JoingDate, ResignedDate)  + '' + DATEDIFF(DAY, JoingDate, ResignedDate) As TotalService from NameofTable.`

But have no luck any Idea .

Regards,

Wednesday, January 27, 2016 4:56 AM

• User-1716253493 posted

You need to get totaldays using datediff, you know 1 year is 365 days and 1 month is 30 days

You can't use datediff(year, month, day) together

1 Jan 2015 - 2 Jan 2016 (1 year + 1 day) instead of 1 year + 12 month + 366 days

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Wednesday, January 27, 2016 6:59 AM
• Marked as answer by Thursday, October 7, 2021 12:00 AM
Wednesday, January 27, 2016 7:49 AM

### All replies

• User364663285 posted

Hi,
No need to compare year, month, date separately. You can generally compare two dates to get the difference in days only.

Wednesday, January 27, 2016 5:11 AM
• User1623409651 posted

I have to display the total service of an employee in years , months and days. can we calcualate the year , months and days for totaldays as you suggested.

I tired this

```DECLARE  @Dateofleaving DATETIME = '2017-08-15 08:30:00'
DECLARE @DateofJoining DATETIME = '2016-08-15 08:30:00'
SELECT CAST(DATEDIFF(year, @DateofJoining,@Dateofleaving) As NVARCHAR(50)) + ' ' + 'Year' + ' , ' +
CAST(DATEDIFF(MONTH, @DateofJoining,@Dateofleaving) As NVARCHAR(50)) + ' ' + 'Months' + ' and ' +
CAST(DATEDIFF(DAY, @DateofJoining,@Dateofleaving) As NVARCHAR(50)) + ' Days'
As Totalservice```

Regards,

Wednesday, January 27, 2016 5:19 AM
• User-1716253493 posted

You need to get totaldays using datediff, you know 1 year is 365 days and 1 month is 30 days

You can't use datediff(year, month, day) together

1 Jan 2015 - 2 Jan 2016 (1 year + 1 day) instead of 1 year + 12 month + 366 days

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Wednesday, January 27, 2016 6:59 AM
• Marked as answer by Thursday, October 7, 2021 12:00 AM
Wednesday, January 27, 2016 7:49 AM