Home Forums SQL Server 2008 T-SQL (SS2K8) Is there an accurate script for datediff format in years:months:days? RE: Is there an accurate script for datediff format in years:months:days?

  • declare @date1 date = '2010-05-17'

    declare @date2 date = '2013-01-16'

    declare @years int

    declare @months int

    declare @days int

    select @years = DATEDIFF(YEAR,@date1,@date2) - case when datepart(day,@date1) > datepart(day,@date2) then 1 else 0 end

    select @months = DATEdiff(month,@date1,@date2) % 12 - case when datepart(day,@date1) > datepart(day,@date2) then 1 else 0 end

    select @months = case when @months >= 0 then @months else 12 + @months end

    select @days = DATEPART(dy,@date2) - DATEPART(dy,@date1)

    select @days = case when @days >=0 then @days else DATEDIFF(DAY,DATEADD(month,-1,@date2),@date2)+@days end

    select @years as [@years], @months as [@months], @days as [@days]

    Gives 2 years, 7 months, -90 days