locked
Difference between two dates in Years, Months and days RRS feed

  • 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

Answers

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

    Thanks Wmec for fast reply,

    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 Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 27, 2016 6:59 AM
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 27, 2016 7:49 AM