SQL Server - Como calcular a diferença em anos (idade) entre duas datas utilizando T-SQL ou CLR

Visualizações: 15.837 views

Neste artigo

Tempo de Leitura: 4 minutos

Olá pessoal,
Tudo bem ?

Neste post eu gostaria de mostrar como calcular a diferença em anos (idade) entre duas datas no SQL Server, mostrando algumas soluções e uma análise de performance entre essas opções a fim de identificar a forma mais performática de realizar esse cálculo.

Como vocês sabem, para resolver esse questão não basta realizar um simples DATEDIFF(YEAR, @Dt_Inicial, @Dt_Final), uma vez que a função DATEDIFF(YEAR…) vai retornar a diferença de anos sem considerar se as datas já “completaram aniversário”.

Numa explicação simples, a função DATEDIFF(YEAR…) retorna o cálculo YEAR(@Dt_Final) – YEAR(@Dt_Inicial).

Vou demonstrar porque isso não funciona para cálculo de idade:

Como vocês puderam observar, não basta usar a função DATEDIFF(YEAR..) para calcular a idade. No primeiro exemplo, com apenas 1 dia de diferença entre as datas, a função me retornou que essa diferença é de 1 ano.

No segundo exemplo, a função me retorna 30 anos de diferença, mas a primeira data é do dia 09/04 e a segunda é 28/05, ou seja, ainda não completou mais 1 ano e o valor correto seria 29 anos.

Como calcular idade usando T-SQL

Agora vou demonstrar duas formas simples para cálculo de idade utilizando Transact-SQL

Cálculo de idade utilizando Transact-SQL e CASE

Para realizar esse cálculo, calculamos a diferença de anos entre as datas e diminuímos 1 caso o mês e dia da data inicial seja menor que o mês e dia da data final.

CREATE FUNCTION [dbo].[fncCalcula_Idade] (@Dt_Nascimento DATETIME, @Dt_Hoje DATETIME)
RETURNS INT
AS BEGIN
  RETURN DATEDIFF(YEAR, @Dt_Nascimento, @Dt_Hoje) + CASE WHEN (MONTH(@Dt_Nascimento) > MONTH(@Dt_Hoje) OR (MONTH(@Dt_Nascimento) = MONTH(@Dt_Hoje) AND DAY(@Dt_Nascimento) > DAY(@Dt_Hoje))) THEN -1 ELSE 0 END
END

CREATE FUNCTION [dbo].[fncCalcula_Idade] (@Dt_Nascimento DATETIME, @Dt_Hoje DATETIME)

RETURNS INT

AS BEGIN

RETURN DATEDIFF(YEAR, @Dt_Nascimento, @Dt_Hoje) + CASE WHEN (MONTH(@Dt_Nascimento) > MONTH(@Dt_Hoje) OR (MONTH(@Dt_Nascimento) = MONTH(@Dt_Hoje) AND DAY(@Dt_Nascimento) > DAY(@Dt_Hoje))) THEN -1 ELSE 0 END

END

Cálculo de idade utilizando Transact-SQL e divisão por 365.25

Para realizar esse cálculo, calculamos a diferença de dias entre as datas e dividimos esse valor por 365,25. Mas porquê esse 0,25? A resposta é simples.. Anos bissextos. Essa “técnica” é de autoria do meu amigo e especialista em BI, Lucas Galon.

CREATE FUNCTION [dbo].[fncCalcula_Idade2] (@Dt_Nascimento DATETIME, @Dt_Hoje DATETIME)
RETURNS INT
AS BEGIN
  RETURN DATEDIFF(DAY, @Dt_Nascimento, @Dt_Hoje) / 365.25
END

CREATE FUNCTION [dbo].[fncCalcula_Idade2] (@Dt_Nascimento DATETIME, @Dt_Hoje DATETIME)

RETURNS INT

AS BEGIN

RETURN DATEDIFF(DAY, @Dt_Nascimento, @Dt_Hoje) / 365.25

END

Como calcular idade usando CLR

Agora vou demonstrar duas formas simples para cálculo de idade utilizando o CLR, que vista obter um grande ganho de performance em virtude das otimizações da linguagem C# e do Microsoft .NET Framework.

Cálculo de idade utilizando CLR e cálculos manuais

Para realizar esse cálculo, calculamos a diferença de anos entre as datas e diminuímos 1 caso o mês e dia da data inicial seja menor que o mês e dia da data final.

using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 fncCalcula_Idade(SqlDateTime Dt_Nascimento, SqlDateTime Dt_Hoje)
    {

        if (Dt_Nascimento.IsNull || Dt_Hoje.IsNull)
            return SqlInt32.Null;

        var anos = Dt_Hoje.Value.Year - Dt_Nascimento.Value.Year;

        if (Dt_Hoje.Value.Month < Dt_Nascimento.Value.Month || (Dt_Hoje.Value.Month == Dt_Nascimento.Value.Month && Dt_Hoje.Value.Day < Dt_Nascimento.Value.Day))
            anos--;

        return anos;
    }
}

using System.Data.SqlTypes;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlInt32 fncCalcula_Idade(SqlDateTime Dt_Nascimento, SqlDateTime Dt_Hoje)

{

if (Dt_Nascimento.IsNull || Dt_Hoje.IsNull)

return SqlInt32.Null;

var anos = Dt_Hoje.Value.Year - Dt_Nascimento.Value.Year;

if (Dt_Hoje.Value.Month < Dt_Nascimento.Value.Month || (Dt_Hoje.Value.Month == Dt_Nascimento.Value.Month && Dt_Hoje.Value.Day < Dt_Nascimento.Value.Day))

anos--;

return anos;

}

Cálculo de idade utilizando CLR e divisão por 365.25

Para realizar esse cálculo, calculamos a diferença de dias entre as datas e dividimos esse valor por 365.25, conforme já falado acima, mas agora utilizando o CLR.

using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 fncCalcula_Idade2(SqlDateTime Dt_Nascimento, SqlDateTime Dt_Hoje)
    {
        return (Dt_Nascimento.IsNull || Dt_Hoje.IsNull) ? SqlInt32.Null : Convert.ToInt32(Math.Floor((Dt_Hoje.Value - Dt_Nascimento.Value).TotalDays / 365.25));
    }
}

using System;

using System.Data.SqlTypes;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlInt32 fncCalcula_Idade2(SqlDateTime Dt_Nascimento, SqlDateTime Dt_Hoje)

{

return (Dt_Nascimento.IsNull || Dt_Hoje.IsNull) ? SqlInt32.Null : Convert.ToInt32(Math.Floor((Dt_Hoje.Value - Dt_Nascimento.Value).TotalDays / 365.25));

}

Resultados das funções apresentadas

Testes de Performance

Agora que demonstrei algumas soluções diferentes para cálculo de idade no SQL Server, vou comparar a performance de todas elas e verificar qual é a mais performática.

Como eu já havia comentado no post SQL Server – Comparação de performance entre Scalar Function e CLR Scalar Function, funções CLR geralmente terão uma performance melhor que funções T-SQL, ao mesmo tempo que funções inline (sem criar UDF) geralmente apresentam uma performance melhor que funções CLR. Vamos ver como cada solução se sai.

Para esses testes, vou utilizar uma base de 131.072 datas aleatórias para calcular a idade comparando com a data atual, e o script para gerar essa base eu vou disponibilizar logo abaixo:

Script para geração da massa de testes

IF (OBJECT_ID('dbo.fncRand') IS NOT NULL) DROP FUNCTION dbo.fncRand
GO
 
CREATE FUNCTION dbo.fncRand(@Numero BIGINT)
RETURNS BIGINT
AS
BEGIN
    RETURN (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * @Numero
END
GO


IF (OBJECT_ID('dbo.Datas') IS NOT NULL) DROP TABLE dbo.Datas
CREATE TABLE dbo.Datas (
    Data_Inicio DATETIME
)

DECLARE
    @Contador INT = 1,
    @Total INT = 17


INSERT INTO dbo.Datas ( Data_Inicio )
VALUES ( '1970-01-01' )


WHILE(@Contador <= @Total)
BEGIN
    

    INSERT INTO dbo.Datas ( Data_Inicio )
    SELECT 
        DATEADD(SECOND, dbo.fncRand(1491696000), '1970-01-01')
    FROM 
        dbo.Datas


    SET @Contador += 1


END

IF (OBJECT_ID('dbo.fncRand') IS NOT NULL) DROP FUNCTION dbo.fncRand

CREATE FUNCTION dbo.fncRand(@Numero BIGINT)

RETURNS BIGINT

BEGIN

RETURN (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * @Numero

END

IF (OBJECT_ID('dbo.Datas') IS NOT NULL) DROP TABLE dbo.Datas

CREATE TABLE dbo.Datas (

Data_Inicio DATETIME

)

DECLARE

@Contador INT = 1,

@Total INT = 17

INSERT INTO dbo.Datas ( Data_Inicio )

VALUES ( '1970-01-01' )

WHILE(@Contador <= @Total)

BEGIN

INSERT INTO dbo.Datas ( Data_Inicio )

SELECT

DATEADD(SECOND, dbo.fncRand(1491696000), '1970-01-01')

FROM

dbo.Datas

SET @Contador += 1

END

Script para realização dos testes

-- Funções inline
SELECT DATEDIFF(YEAR, Data_Inicio, GETDATE()) + CASE WHEN (MONTH(Data_Inicio) > MONTH(GETDATE()) OR (MONTH(Data_Inicio) = MONTH(GETDATE()) AND DAY(Data_Inicio) > DAY(GETDATE()))) THEN -1 ELSE 0 END
FROM dbo.Datas

SELECT CAST(DATEDIFF(DAY, Data_Inicio, GETDATE()) / 365.25 AS INT)
FROM dbo.Datas

-- Funções T-SQL UDF
SELECT dbo.fncCalcula_Idade(Data_Inicio, GETDATE())
FROM dbo.Datas

SELECT dbo.fncCalcula_Idade2(Data_Inicio, GETDATE())
FROM dbo.Datas

-- Funções CLR
SELECT CLR.dbo.fncCalcula_Idade(Data_Inicio, GETDATE())
FROM dbo.Datas

SELECT CLR.dbo.fncCalcula_Idade2(Data_Inicio, GETDATE())
FROM dbo.Datas

-- Funções inline

SELECT DATEDIFF(YEAR, Data_Inicio, GETDATE()) + CASE WHEN (MONTH(Data_Inicio) > MONTH(GETDATE()) OR (MONTH(Data_Inicio) = MONTH(GETDATE()) AND DAY(Data_Inicio) > DAY(GETDATE()))) THEN -1 ELSE 0 END

FROM dbo.Datas

SELECT CAST(DATEDIFF(DAY, Data_Inicio, GETDATE()) / 365.25 AS INT)

FROM dbo.Datas

-- Funções T-SQL UDF

SELECT dbo.fncCalcula_Idade(Data_Inicio, GETDATE())

FROM dbo.Datas

SELECT dbo.fncCalcula_Idade2(Data_Inicio, GETDATE())

FROM dbo.Datas

-- Funções CLR

SELECT CLR.dbo.fncCalcula_Idade(Data_Inicio, GETDATE())

FROM dbo.Datas

SELECT CLR.dbo.fncCalcula_Idade2(Data_Inicio, GETDATE())

FROM dbo.Datas

Resultados:

Como o esperado, as funções inline apresentaram a melhor performance, com o CLR apresentando uma performance parecida e as funções T-SQL demorando muito mais que as outras 2 soluções. Além disso, a solução com divisão por 365.25 foi melhor que o uso de CASE em todos os 3 cenários.

É isso aí, pessoal!
Espero que vocês tenham aprendido a calcular a idade corretamente e com a melhor performance possível.
Um abraço e até a próxima.

Assinar blog por e-mail

Visualizações do Blog

Microsoft MVP

Arquivo de Posts

Categorias

Posts recentes

SQL Server – Como calcular a diferença em anos (idade) entre duas datas utilizando T-SQL ou CLR

Como calcular idade usando T-SQL

Cálculo de idade utilizando Transact-SQL e CASE

Cálculo de idade utilizando Transact-SQL e divisão por 365.25

Como calcular idade usando CLR

Cálculo de idade utilizando CLR e cálculos manuais

Cálculo de idade utilizando CLR e divisão por 365.25

Testes de Performance

You may also like...

2 Responses

Deixe uma respostaCancelar resposta

Como calcular idade usando T-SQL

Cálculo de idade utilizando Transact-SQL e CASE

Cálculo de idade utilizando Transact-SQL e divisão por 365.25

Como calcular idade usando CLR

Cálculo de idade utilizando CLR e cálculos manuais

Cálculo de idade utilizando CLR e divisão por 365.25

Testes de Performance

You may also like...

SQL Server – Como melhorar a formatação da saída das consultas combinadas com SET STATISTICS IO e TIME

SQL Server – Como identificar a data que a licença trial / evaluation vai expirar

SQL Server – Como ativar/habilitar o usuário sa

2 Responses

Deixe uma respostaCancelar resposta