Calculate Difference between Dates in SQL Server, with Years, Months and Days output

Question

What would be the SQL (SQLSERVER) that could extract the difference between two dates (the largest being the current date of the system) with output of Years, Months and Days?

Answer 1

I adapted the solution of another answer mine:

SELECT
   DataAdmissao,
   Hoje,
   YEAR( Tempo ) - 1 AS Anos,
   MONTH( Tempo ) - 1 AS Meses,
   DAY( Tempo ) - 1 AS FCCDias

FROM (
  SELECT
     DataAdmissao,
     CAST( GetDate() AS date ) AS Hoje,
     DATEADD( day, -DAY( DataAdmissao ) + 1,
        DATEADD( month, -MONTH( DataAdmissao ) + 1,
           DATEADD( year, -YEAR( DataAdmissao ) + 1,
              CAST( GetDate() AS date )
           )
        )
     ) AS Tempo FROM TabelaExemplo
  ) AS T;

If you want more details, it’s worth it click here and read the observations left.

See working on SQL Fiddle

Answer 2

SQL Creation of the Table

CREATE TABLE [tablexemplo](
    [DataAdmissao] [date] NULL
) ON [PRIMARY];

INSERT INTO tablexemplo values('2010-01-30');
INSERT INTO tablexemplo values('2010-02-28');
INSERT INTO tablexemplo values('2010-03-30');
INSERT INTO tablexemplo values('2010-06-29');
INSERT INTO tablexemplo values(GetDate());

SQL

SELECT 
    DataAdmissao,
    CASE WHEN Mes < 0 THEN
        (Ano - 1)   
    ELSE
        Ano
    END as Ano,
    CASE WHEN Mes < 0 THEN
        (Mes + 12)  
    ELSE
        Mes
    END as Mes,
    Dia
FROM (
    SELECT 
        DataAdmissao, 
        Ano, 
        CASE WHEN Dia < 0 THEN
            (Dia + 30)  
        ELSE
            Dia
        END as Dia,
        CASE WHEN Dia < 0 THEN
            (Mes - 1)   
        ELSE
            Mes
        END as Mes
    FROM (
        SELECT DataAdmissao, 
            (YEAR(GETDATE()) - YEAR(DataAdmissao)) as Ano,
            (MONTH(GETDATE()) - MONTH(DataAdmissao)) as Mes,
            (DAY(GETDATE()) - DAY(DataAdmissao)) as Dia
        FROM tablexemplo 
        ) AS T
    )T1 

Example: Sqlfiddle

Answer 3

I formatted a query to list the correct results, always considering the correct differences between months and days as well.

DECLARE @DifDataIn as datetime, @DifDataFi as datetime
SET @DifDataIn = '1988-12-22'
SET @DifDataFi = '2015-11-27'

SELECT 
    -- Cálculo dos Anos
    Case
        When  month(@DifDataIn) < month(@DifDataFi) or
            ( month(@DifDataIn) = month(@DifDataFi) and day(@DifDataIn) <= day(@DifDataFi) )
            THEN datediff(year,@DifDataIn,@DifDataFi)
        Else 
            Case
                When year(@DifDataIn) = year(@DifDataFi) then 0
                Else datediff(year,@DifDataIn,@DifDataFi) - 1
            End
    End as ANOS,
    -- Cálculo dos Meses
    Case
        When day(@DifDataFi) >= day(@DifDataIn) then
            datediff(month,@DifDataIn,@DifDataFi) - (floor(datediff(month,@DifDataIn,@DifDataFi) /12) * 12)
        Else
            datediff(month,@DifDataIn,@DifDataFi) - (floor(datediff(month,@DifDataIn,@DifDataFi) /12) * 12) -1
    End as MESES,
    -- Cálculo dos Dias
    Case
        When day(@DifDataFi) >= day(@DifDataIn) then day(@DifDataFi) - day(@DifDataIn)
        Else datediff(day, ( cast(year(@DifDataFi) as varchar(4)) +'-'+ cast(month(@DifDataFi)-1 as varchar(2)) +'-'+ cast(day(@DifDataIn) as varchar(2)) ),
                           ( cast(year(@DifDataFi) as varchar(4)) +'-'+ cast(month(@DifDataFi)   as varchar(2)) +'-'+ cast(day(@DifDataFi) as varchar(2)) )
                     )
    End as DIAS