Comment calculer l'âge (en années) en fonction de la Date de naissance et getDate()
J'ai une table listant les personnes avec leur date de naissance(actuellement un nvarchar (25))
Comment puis-je convertir une date, puis calculer leur âge en années?
Mes données se présente comme suit
ID Name DOB
1 John 1992-01-09 00:00:00
2 Sally 1959-05-20 00:00:00
Je voudrais voir:
ID Name AGE DOB
1 John 17 1992-01-09 00:00:00
2 Sally 50 1959-05-20 00:00:00
30 réponses
Il y a des problèmes avec l'année bissextile / jours et la méthode suivante, voir la mise à jour ci-dessous:
, Essayez ceci:
DECLARE @dob datetime SET @dob='1992-01-09 00:00:00' SELECT DATEDIFF(hour,@dob,GETDATE())/8766.0 AS AgeYearsDecimal ,CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0)) AS AgeYearsIntRound ,DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc
Sortie:
AgeYearsDecimal AgeYearsIntRound AgeYearsIntTrunc --------------------------------------- ---------------- ---------------- 17.767054 18 17 (1 row(s) affected)
UPDATE Voici quelques méthodes plus précises:
LA MEILLEURE MÉTHODE POUR LES ANNÉES DANS INT
DECLARE @Now datetime, @Dob datetime
SELECT @Now='1990-05-05', @Dob='1980-05-05' --results in 10
--SELECT @Now='1990-05-04', @Dob='1980-05-05' --results in 9
--SELECT @Now='1989-05-06', @Dob='1980-05-05' --results in 9
--SELECT @Now='1990-05-06', @Dob='1980-05-05' --results in 10
--SELECT @Now='1990-12-06', @Dob='1980-05-05' --results in 10
--SELECT @Now='1991-05-04', @Dob='1980-05-05' --results in 10
SELECT
(CONVERT(int,CONVERT(char(8),@Now,112))-CONVERT(char(8),@Dob,112))/10000 AS AgeIntYears
Vous pouvez changer le 10000
ci-dessus en 10000.0
et obtenir des décimales, mais ce ne sera pas aussi précis que la méthode ci-dessous.
MEILLEURE MÉTHODE POUR LES ANNÉES EN DÉCIMAL
DECLARE @Now datetime, @Dob datetime
SELECT @Now='1990-05-05', @Dob='1980-05-05' --results in 10.000000000000
--SELECT @Now='1990-05-04', @Dob='1980-05-05' --results in 9.997260273973
--SELECT @Now='1989-05-06', @Dob='1980-05-05' --results in 9.002739726027
--SELECT @Now='1990-05-06', @Dob='1980-05-05' --results in 10.002739726027
--SELECT @Now='1990-12-06', @Dob='1980-05-05' --results in 10.589041095890
--SELECT @Now='1991-05-04', @Dob='1980-05-05' --results in 10.997260273973
SELECT 1.0* DateDiff(yy,@Dob,@Now)
+CASE
WHEN @Now >= DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)) THEN --birthday has happened for the @now year, so add some portion onto the year difference
( 1.0 --force automatic conversions from int to decimal
* DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
/ DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
)
ELSE --birthday has not been reached for the last year, so remove some portion of the year difference
-1 --remove this fractional difference onto the age
* ( -1.0 --force automatic conversions from int to decimal
* DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
/ DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
)
END AS AgeYearsDecimal
Je dois jeter celui-ci là-bas. Si vous convertissez la date en utilisant le style 112 (AAAAMMJJ) en un nombre, vous pouvez utiliser un calcul comme celui-ci...
(aaaammjj-aaaammjj) / 10000 = différence en années complètes
declare @as_of datetime, @bday datetime;
select @as_of = '2009/10/15', @bday = '1980/4/20'
select
Convert(Char(8),@as_of,112),
Convert(Char(8),@bday,112),
0 + Convert(Char(8),@as_of,112) - Convert(Char(8),@bday,112),
(0 + Convert(Char(8),@as_of,112) - Convert(Char(8),@bday,112)) / 10000
Sortie
20091015 19800420 290595 29
J'ai utilisé cette requête dans notre code de production depuis près de 10 ans:
SELECT FLOOR((CAST (GetDate() AS INTEGER) - CAST(Date_of_birth AS INTEGER)) / 365.25) AS Age
Tant de solutions ci-dessus sont fausses DateDiff(yy,@Dob, @PassedDate) ne considérera pas le mois et le jour des deux dates. Prendre également les pièces de fléchettes et comparer ne fonctionne que si elles sont correctement commandées.
LE CODE SUIVANT FONCTIONNE ET EST TRÈS SIMPLE:
create function [dbo].[AgeAtDate](
@DOB datetime,
@PassedDate datetime
)
returns int
with SCHEMABINDING
as
begin
declare @iMonthDayDob int
declare @iMonthDayPassedDate int
select @iMonthDayDob = CAST(datepart (mm,@DOB) * 100 + datepart (dd,@DOB) AS int)
select @iMonthDayPassedDate = CAST(datepart (mm,@PassedDate) * 100 + datepart (dd,@PassedDate) AS int)
return DateDiff(yy,@DOB, @PassedDate)
- CASE WHEN @iMonthDayDob <= @iMonthDayPassedDate
THEN 0
ELSE 1
END
End
Vous devez considérer la façon dont les tours de commande datediff.
SELECT CASE WHEN dateadd(year, datediff (year, DOB, getdate()), DOB) > getdate()
THEN datediff(year, DOB, getdate()) - 1
ELSE datediff(year, DOB, getdate())
END as Age
FROM <table>
Que j'ai adapté de ici
EDIT: CETTE RÉPONSE EST INCORRECTE. je le laisse ici comme un avertissement à toute personne tentée d'utiliser dayofyear
, avec une autre modification à la fin.
Si, comme moi, vous ne voulez pas diviser par jours fractionnaires ou risquer des erreurs d'arrondi / année bissextile, j'applaudis @ bacon Bits commentaire dans un post ci-dessus https://stackoverflow.com/a/1572257/489865 où il dit:
Si nous parlons d'âges humains, vous devriez le calculer de la manière suivante les humains calculent âge. Cela n'a rien à voir avec la vitesse de la terre se déplace et tout à voir avec le calendrier. À chaque fois le même le mois et le jour s'écoulent comme date de naissance, vous augmentez l'âge de 1. Cela signifie que ce qui suit est le plus précis car il reflète ce que les humains veulent dire quand ils disent "âge".
Il offre alors:
DATEDIFF(yy, @date, GETDATE()) -
CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE()))
THEN 1 ELSE 0 END
Il y a plusieurs suggestions ici impliquant de comparer le mois et le jour (et certains se trompent, ne permettant pas le OR
aussi correctement ici!). Mais personne n'a proposé dayofyear
, ce qui semble si simple et beaucoup plus court. J'offre:
DATEDIFF(year, @date, GETDATE()) -
CASE WHEN DATEPART(dayofyear, @date) > DATEPART(dayofyear, GETDATE()) THEN 1 ELSE 0 END
[Note: nulle part dans SQL BOL / MSDN est ce que DATEPART(dayofyear, ...)
retourne réellement documenté! Je comprends que ce soit un nombre dans la plage 1--366; plus important encore, il nePas change par locale selon DATEPART(weekday, ...)
& SET DATEFIRST
.]
Modifier: Pourquoi dayofyear
va mal: comme l'a commenté l'utilisateur @AeroX, si la date de naissance/début est après février dans une année non bissextile, l'âge est incrémenté d'un jour tôt lorsque la date actuelle / de fin est une année bissextile, par exemple '2015-05-26'
, '2016-05-25'
donne un âge de 1 quand il devrait encore être 0. Comparer le dayofyear
dans différentes années est clairement dangereux. Donc, utiliser MONTH()
et DAY()
est nécessaire après tout.
Comme il n'y a pas une réponse simple qui donne toujours l'âge correct, voici ce que j'ai trouvé.
SELECT DATEDIFF(YY, DateOfBirth, GETDATE()) -
CASE WHEN RIGHT(CONVERT(VARCHAR(6), GETDATE(), 12), 4) >=
RIGHT(CONVERT(VARCHAR(6), DateOfBirth, 12), 4)
THEN 0 ELSE 1 END AS AGE
Cela obtient la différence d'année entre la date de naissance et la date actuelle. Ensuite, il soustrait une année si la date de naissance n'est pas encore passée.
Précis tout le temps-indépendamment des années bissextiles ou de la proximité de la date de naissance.
Le Meilleur de tous - pas de fonction.
Qu'en est-il de:
DECLARE @DOB datetime
SET @DOB='19851125'
SELECT Datepart(yy,convert(date,GETDATE())-@DOB)-1900
Cela n'éviterait-il pas tous ces problèmes d'arrondi, de tronçonnage et de mise en forme?
Je crois que cela est similaire à d'autres affichés ici.... mais cette solution a fonctionné pour l'année bissextile exemples 02/29/1976 à 03/01/2011 et a également travaillé pour le cas de la première année.. comme 07/04/2011 à 07/03/2012 que le dernier a posté sur la solution de l'année bissextile n'a pas fonctionné pour ce cas d'utilisation de la première année.
SELECT FLOOR(DATEDIFF(DAY, @date1 , @date2) / 365.25)
A ici.
Vérifiez simplement si la réponse ci-dessous est réalisable.
DECLARE @BirthDate DATE = '09/06/1979'
SELECT
(
YEAR(GETDATE()) - YEAR(@BirthDate) -
CASE WHEN (MONTH(GETDATE()) * 100) + DATEPART(dd, GETDATE()) >
(MONTH(@BirthDate) * 100) + DATEPART(dd, @BirthDate)
THEN 1
ELSE 0
END
)
SELECT ID,
Name,
DATEDIFF(yy,CONVERT(DATETIME, DOB),GETDATE()) AS AGE,
DOB
FROM MyTable
DECLARE @DOB datetime
set @DOB ='11/25/1985'
select floor(
( cast(convert(varchar(8),getdate(),112) as int)-
cast(convert(varchar(8),@DOB,112) as int) ) / 10000
)
Source: http://beginsql.wordpress.com/2012/04/26/how-to-calculate-age-in-sql-server/
J'ai beaucoup réfléchi et cherché à ce sujet et j'ai 3 solutions qui
- calculer l'âge correctement
- sont courts (la plupart du temps)
- sont (pour la plupart) très compréhensibles.
Voici les valeurs de test:
DECLARE @NOW DATETIME = '2013-07-04 23:59:59'
DECLARE @DOB DATETIME = '1986-07-05'
Solution 1:, j'ai trouvé cette approche dans une bibliothèque js. C'est mon préféré.
DATEDIFF(YY, @DOB, @NOW) -
CASE WHEN DATEADD(YY, DATEDIFF(YY, @DOB, @NOW), @DOB) > @NOW THEN 1 ELSE 0 END
En fait, il ajoute une différence d'années à la date de naissance et s'il est plus grand que la date actuelle, il soustrait une année. Simple droit? La seule chose est que la différence dans les années est dupliquée ici.
, Mais si vous n'avez pas besoin d'utiliser inline vous pouvez l'écrire comme ceci:
DECLARE @AGE INT = DATEDIFF(YY, @DOB, @NOW)
IF DATEADD(YY, @AGE, @DOB) > @NOW
SET @AGE = @AGE - 1
Solution 2: {[24] } celui-ci que j'ai copié à l'origine de @bacon-bits. C'est le plus facile à comprendre, mais un peu long.
DATEDIFF(YY, @DOB, @NOW) -
CASE WHEN MONTH(@DOB) > MONTH(@NOW)
OR MONTH(@DOB) = MONTH(@NOW) AND DAY(@DOB) > DAY(@NOW)
THEN 1 ELSE 0 END
C'est calculer l'âge comme nous les humains.
Solution 3: mon ami l'a refactorisé dans ceci:
DATEDIFF(YY, @DOB, @NOW) -
CEILING(0.5 * SIGN((MONTH(@DOB) - MONTH(@NOW)) * 50 + DAY(@DOB) - DAY(@NOW)))
Celui - ci est le plus court mais c'est le plus difficile à comprendre. 50
est juste un poids, donc la différence de jour n'est importante que lorsque les mois sont les mêmes. SIGN
la fonction est pour transformer la valeur qu'elle obtient à -1, 0 ou 1. {[7] } est le même que Math.max(0, value)
mais il n'y a pas une telle chose dans SQL.
CASE WHEN datepart(MM, getdate()) < datepart(MM, BIRTHDATE) THEN ((datepart(YYYY, getdate()) - datepart(YYYY, BIRTH_DATE)) -1 )
ELSE
CASE WHEN datepart(MM, getdate()) = datepart(MM, BIRTHDATE)
THEN
CASE WHEN datepart(DD, getdate()) < datepart(DD, BIRTHDATE) THEN ((datepart(YYYY, getdate()) - datepart(YYYY, BIRTHDATE)) -1 )
ELSE (datepart(YYYY, getdate()) - datepart(YYYY, BIRTHDATE))
END
ELSE (datepart(YYYY, getdate()) - datepart(YYYY, BIRTHDATE)) END
END
Que diriez-vous de ceci:
SET @Age = CAST(DATEDIFF(Year, @DOB, @Stamp) as int)
IF (CAST(DATEDIFF(DAY, DATEADD(Year, @Age, @DOB), @Stamp) as int) < 0)
SET @Age = @Age - 1
Essayez Ceci
DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '08/16/84'
SELECT @tmpdate = @date
SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())
SELECT Convert(Varchar(Max),@years)+' Years '+ Convert(Varchar(max),@months) + ' Months '+Convert(Varchar(Max), @days)+'days'
Essayez cette solution:
declare @BirthDate datetime
declare @ToDate datetime
set @BirthDate = '1/3/1990'
set @ToDate = '1/2/2008'
select @BirthDate [Date of Birth], @ToDate [ToDate],(case when (DatePart(mm,@ToDate) < Datepart(mm,@BirthDate))
OR (DatePart(m,@ToDate) = Datepart(m,@BirthDate) AND DatePart(dd,@ToDate) < Datepart(dd,@BirthDate))
then (Datepart(yy, @ToDate) - Datepart(yy, @BirthDate) - 1)
else (Datepart(yy, @ToDate) - Datepart(yy, @BirthDate))end) Age
Cela gérera correctement les problèmes avec l'anniversaire et l'arrondi:
DECLARE @dob datetime
SET @dob='1992-01-09 00:00:00'
SELECT DATEDIFF(YEAR, '0:0', getdate()-@dob)
La solution D'Ed Harper est la plus simple que j'ai trouvée qui ne renvoie jamais la mauvaise réponse lorsque le mois et le jour des deux dates sont séparés de 1 jour ou moins. J'ai fait une légère modification pour gérer les âges négatifs.
DECLARE @D1 AS DATETIME, @D2 AS DATETIME
SET @D2 = '2012-03-01 10:00:02'
SET @D1 = '2013-03-01 10:00:01'
SELECT
DATEDIFF(YEAR, @D1,@D2)
+
CASE
WHEN @D1<@D2 AND DATEADD(YEAR, DATEDIFF(YEAR,@D1, @D2), @D1) > @D2
THEN - 1
WHEN @D1>@D2 AND DATEADD(YEAR, DATEDIFF(YEAR,@D1, @D2), @D1) < @D2
THEN 1
ELSE 0
END AS AGE
La réponse marquée comme correcte est plus proche de la précision, mais elle échoue dans le scénario suivant-où L'année de naissance est L'année bissextile et le jour sont après le mois de février
declare @ReportStartDate datetime = CONVERT(datetime, '1/1/2014'),
@DateofBirth datetime = CONVERT(datetime, '2/29/1948')
FLOOR(DATEDIFF(HOUR,@DateofBirth,@ReportStartDate )/8766)
OU
FLOOR(DATEDIFF(HOUR,@DateofBirth,@ReportStartDate )/8765.82) -- Divisor is more accurate than 8766
-- la solution suivante me donne des résultats plus précis.
FLOOR(DATEDIFF(YEAR,@DateofBirth,@ReportStartDate) - (CASE WHEN DATEADD(YY,DATEDIFF(YEAR,@DateofBirth,@ReportStartDate),@DateofBirth) > @ReportStartDate THEN 1 ELSE 0 END ))
Cela a fonctionné dans presque tous les scénarios, compte tenu de l'année bissextile, de la date du 29 février, etc.
Veuillez me corriger si cette formule a une échappatoire.
Declare @dob datetime
Declare @today datetime
Set @dob = '05/20/2000'
set @today = getdate()
select CASE
WHEN dateadd(year, datediff (year, @dob, @today), @dob) > @today
THEN datediff (year, @dob, @today) - 1
ELSE datediff (year, @dob, @today)
END as Age
Voici comment je calcule l'âge donné une date de naissance et la date actuelle.
select case
when cast(getdate() as date) = cast(dateadd(year, (datediff(year, '1996-09-09', getdate())), '1996-09-09') as date)
then dateDiff(yyyy,'1996-09-09',dateadd(year, 0, getdate()))
else dateDiff(yyyy,'1996-09-09',dateadd(year, -1, getdate()))
end as MemberAge
go
CREATE function dbo.AgeAtDate(
@DOB datetime,
@CompareDate datetime
)
returns INT
as
begin
return CASE WHEN @DOB is null
THEN
null
ELSE
DateDiff(yy,@DOB, @CompareDate)
- CASE WHEN datepart(mm,@CompareDate) > datepart(mm,@DOB) OR (datepart(mm,@CompareDate) = datepart(mm,@DOB) AND datepart(dd,@CompareDate) >= datepart(dd,@DOB))
THEN 0
ELSE 1
END
END
End
GO
DECLARE @FromDate DATETIME = '1992-01-2623:59:59.000',
@ToDate DATETIME = '2016-08-10 00:00:00.000',
@Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME
SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
- (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
@FromDate) > @ToDate THEN 1 ELSE 0 END)
SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
SET @Months = DATEDIFF(MONTH, @tmpFromDate, @ToDate)
- (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
@tmpFromDate) > @ToDate THEN 1 ELSE 0 END)
SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
SET @Days = DATEDIFF(DAY, @tmpFromDate, @ToDate)
- (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
@tmpFromDate) > @ToDate THEN 1 ELSE 0 END)
SELECT @FromDate FromDate, @ToDate ToDate,
@Years Years, @Months Months, @Days Days
Qu'en est-il d'une solution avec uniquement des fonctions de date, pas de mathématiques, pas de soucis pour l'année bissextile
CREATE FUNCTION dbo.getAge(@dt datetime)
RETURNS int
AS
BEGIN
RETURN
DATEDIFF(yy, @dt, getdate())
- CASE
WHEN
MONTH(@dt) > MONTH(GETDATE()) OR
(MONTH(@dt) = MONTH(GETDATE()) AND DAY(@dt) > DAY(GETDATE()))
THEN 1
ELSE 0
END
END
Après avoir essayé de nombreuses méthodes, cela fonctionne 100% du temps en utilisant la fonction de format MS SQL moderne au lieu de convertir en style 112. L'un ou l'autre fonctionnerait mais c'est le moins de code.
Quelqu'un peut-il trouver une combinaison de date qui ne fonctionne pas? Je ne pense pas qu'il y en ait un:)
--Set parameters, or choose from table.column instead:
DECLARE @DOB DATE = '2000/02/29' -- If @DOB is a leap day...
,@ToDate DATE = '2018/03/01' --...there birthday in this calculation will be
--0+ part tells SQL to calc the char(8) as numbers:
SELECT [Age] = (0+ FORMAT(@ToDate,'yyyyMMdd') - FORMAT(@DOB,'yyyyMMdd') ) /10000
Nous avons utilisé quelque chose comme ici, mais en prenant l'âge moyen:
ROUND(avg(CONVERT(int,DATEDIFF(hour,DOB,GETDATE())/8766.0)),0) AS AverageAge
Notez que le tour est à l'extérieur plutôt qu'à l'intérieur. Cela permettra à L'AVG d'être plus précis et nous arrondissons une seule fois. Le rendre plus rapide aussi.
select DATEDIFF(yy,@DATE,GETDATE()) -
case when DATEPART(mm,GETDATE())*100+DATEPART(dd,GETDATE())>=
DATEPART(mm,@DATE)*100+DATEPART(dd,@DATE) THEN 0
ELSE 1 END
select datediff(day,'1991-03-16',getdate()) \\for days,get date refers today date
select datediff(year,'1991-03-16',getdate()) \\for years
select datediff(month,'1991-03-16',getdate()) \\for month