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.
|
1 2 3 4 5 |
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.
|
1 2 3 4 5 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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 |
Script para realização dos testes
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- 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.
And that's it, folks!
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.
Portuguese 
English
Vai ajudar bastante no projeto análise de performance abs.
Cristiano,
Obrigado pelo feedback!
Se você está estudando performance, dê uma lida no post https://www.dirceuresende.com/blog/sql-server-comparacao-de-performance-entre-scalar-function-udf-e-clr-scalar-function/, que talvez te ajude também.
Abraço!