none
How to get the anniversary calculation correct in SQL server 2008 r2 ? RRS feed

  • Question

  • DECLARE @TABLE  table
    (
    userid int,
    Hiredate date
    )
    insert into @TABLE values( 9,cast('2012-06-19' as date)),
    ( 1,cast('2007-06-19' as date)),
    ( 2,cast('2002-06-19' as date)),
    ( 3,cast('1997-06-19' as date)),
    ( 4,cast('1992-06-19' as date)),
    ( 5,cast('1987-06-19' as date)),
    ( 6,cast('1982-06-19' as date)),
    ( 7,cast('1977-06-19' as date)),
    ( 8,cast('1972-06-19' as date))
    select * from @TABLE
    select datediff(year,Hiredate,getdate()) as anniversary from @TABLE

    How to get the anniversary calculation correct in SQL server 2008 r2 ?

    I have to calculate anniversary of user based on the hiredate and currentdate, so  I have used the above code to achieve .Do you think it will be always perfect ?Or it has flaws which I need to correct .

    Note : I m getting Hiredate in YYYY-MM-DD format







    • Edited by MLTC Monday, June 19, 2017 9:48 PM
    Monday, June 19, 2017 9:46 PM

Answers

  • One method is to convert the dates to a yyyymmdd integer, calculate the difference, and divide by 10000.

    SELECT Hiredate,(CAST(CONVERT(char(8), GETDATE(), 112) AS int)-CAST(CONVERT(char(8), Hiredate, 112) AS int))/10000 AS AniversaryYears
    FROM @TABLE;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Xi JinMicrosoft contingent staff Tuesday, June 20, 2017 2:04 AM
    • Marked as answer by MLTC Wednesday, June 21, 2017 5:20 AM
    • Unmarked as answer by MLTC Wednesday, June 21, 2017 5:56 PM
    • Unproposed as answer by MLTC Wednesday, June 21, 2017 5:56 PM
    • Marked as answer by MLTC Wednesday, June 21, 2017 5:59 PM
    Tuesday, June 20, 2017 12:37 AM

All replies

  • If you want the specific date for an anniversary, very similar to what you have but use dateadd instead. Datediff will give you the difference of the year component but not necessarily years.

    For example, the query below will give one year even though there is only one day difference

    select datediff(year, '2016-12-31', '2017-01-01')


    Monday, June 19, 2017 10:02 PM
  • One method is to convert the dates to a yyyymmdd integer, calculate the difference, and divide by 10000.

    SELECT Hiredate,(CAST(CONVERT(char(8), GETDATE(), 112) AS int)-CAST(CONVERT(char(8), Hiredate, 112) AS int))/10000 AS AniversaryYears
    FROM @TABLE;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Xi JinMicrosoft contingent staff Tuesday, June 20, 2017 2:04 AM
    • Marked as answer by MLTC Wednesday, June 21, 2017 5:20 AM
    • Unmarked as answer by MLTC Wednesday, June 21, 2017 5:56 PM
    • Unproposed as answer by MLTC Wednesday, June 21, 2017 5:56 PM
    • Marked as answer by MLTC Wednesday, June 21, 2017 5:59 PM
    Tuesday, June 20, 2017 12:37 AM
  • Try something like this instead...

    SELECT [T].[Hiredate]
          ,[CA].[LastAnniversary]
          ,DATEDIFF(yy, [T].[Hiredate], [CA].[LastAnniversary]) AS [WholeYearsWithCompany]
    FROM @TABLE AS [T]
        CROSS APPLY(
            SELECT [LastAnniversary] = 
                CASE
                    WHEN DATEADD(yy, DATEDIFF(yy, [Hiredate], GETDATE()), [Hiredate]) > GETDATE() 
                    THEN DATEADD(yy, DATEDIFF(yy, [Hiredate], GETDATE()) - 1, [Hiredate])
                    ELSE DATEADD(yy, DATEDIFF(yy, [Hiredate], GETDATE()), [Hiredate])
                END
         ) AS [CA];
    


    Tuesday, June 20, 2017 12:38 AM
  • select datepart(year,getdate()) - datepart(year,Hiredate) from @TABLE

    SQL Server 2016:

    select datediff_big(year,Hiredate,getdate()) as anniversary from @TABLE
    Tuesday, June 20, 2017 4:23 AM