In mijn dagelijkse werk moet ik heel vaak de leeftijd van mensen berekenen en rapporteren. Er zijn een paar manieren om de leeftijd te berekenen die het goede resultaat geven. Een SQL Function raad ik hiervoor af. SQL Functions zijn erg traag, zeker op grote datasets. Met een beetje SQL-code die je snel inbouwt, staat de leeftijd op je scherm. Meestal wil je de leeftijd berekenen zonder decimalen, maar ik heb ook een voorbeeld met decimalen opgenomen. Net als bij het omzetten van een datetime naar een int: gebruik gewone SQL en geen functies!
Hoe bereken je een leeftijd in T-SQL?
Hieronder staan een paar manieren om de leeftijd van een persoon te berekenen in T-SQL (SQL SERVER). Alle berekeningen geven hetzelfde resultaat. Of de ene sneller of trager is dan de andere, weet ik niet.
DECLARE @DateofBirth datetime = CONVERT(datetime, '2/29/1948')DECLARE @ReportStartDate datetime = CONVERT(datetime, '1/1/2014')
SELECT FLOOR(DATEDIFF(HOUR,@DateofBirth,@ReportStartDate )/8766)
SELECT DATEDIFF(hour,@DateofBirth,@ReportStartDate)/8766.0 AS AgeYearsDecimal,DATEDIFF(hour,@DateofBirth,@ReportStartDate)/8766 AS AgeYearsIntTrunc
SELECT (CASE WHEN DATEADD(YEAR, datediff(YEAR, @DateofBirth, @ReportStartDate), @DateofBirth) > @ReportStartDateTHEN DATEDIFF (YEAR, @DateofBirth, @ReportStartDate) - 1ELSE DATEDIFF (YEAR, @DateofBirth, @ReportStartDate)END) AS Age
SELECT FLOOR(DATEDIFF(HOUR,@DateofBirth,@ReportStartDate )/8765.82)
SELECT FLOOR(DATEDIFF(YEAR,@DateofBirth,@ReportStartDate)- (CASE WHEN DATEADD(YY,DATEDIFF(YEAR,@DateofBirth,@ReportStartDate),@DateofBirth) > @ReportStartDate THEN 1 ELSE 0 END ))
Veelgestelde vragen
Hoe bereken ik een leeftijd in T-SQL?
Trek de jaren af met DATEDIFF(YEAR, ...) en haal er 1 vanaf als de verjaardag nog niet geweest is. Dat controleer je met een CASE en DATEADD. Gewone SQL geeft het goede resultaat.
Moet ik een SQL Function gebruiken om een leeftijd te berekenen?
Een SQL Function is traag, zeker op grote datasets. Gebruik gewone SQL in de query zelf.
Hoe bereken ik een leeftijd met decimalen in SQL Server?
Gebruik DATEDIFF(hour, @DateofBirth, @ReportStartDate)/8766.0 en noem de kolom AgeYearsDecimal. Deel je door 8766 zonder de .0, dan krijg je een afgekapt geheel getal.
Hoe bereken ik een leeftijd in hele jaren met DATEDIFF en uren?
Gebruik FLOOR(DATEDIFF(HOUR, @DateofBirth, @ReportStartDate)/8766). Een deler van 8765.82 werkt ook. Allebei geven ze de leeftijd in jaren zonder decimalen.
Geven alle leeftijdsberekeningen in T-SQL hetzelfde resultaat?
Ja. De varianten met DATEDIFF in uren en de CASE met DATEADD geven allemaal dezelfde leeftijd. Of de ene sneller of trager is dan de andere, weet ik niet.
3 reacties
CREATE FUNCTION [dbo].[ufn_GetAge]
(@pDateOfBirth DATETIME,
@pAsOfDate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @vAge INT;
IF @pDateOfBirth >= @pAsOfDate
RETURN 0;
SET @vAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate);
IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate)
OR (MONTH(@pDateOfBirth) = MONTH(@pAsOfDate)
AND DAY(@pDateOfBirth) > DAY(@pAsOfDate))
SET @vAge = @vAge - 1;
RETURN @vAge;
END;
GO
Select DATEDIFF(yy, patient.BirthDate, GETDATE()) - CASE
WHEN MONTH(patient.BirthDate) > MONTH(GETDATE())
OR (MONTH(patient.BirthDate) = MONTH(GETDATE())
AND DAY(patient.BirthDate) > DAY(GETDATE()))
THEN 1
ELSE 0
END AS Age
There is no solution based that will always return correct results based on one DATEDIFF.
For example, the formula DATEDIFF(HOUR,@DateofBirth,@ReportStartDate)/8766 return an incorrect result for date of birth 19 January 1994 and probe date 19 January 2016.
The formula FLOOR(DATEDIFF(HOUR,@DateofBirth,@ReportStartDate )/8765.82) also fails for on a combination like that.
The other formula's are fine, except that the FLOOR() function in the last formula doesn't add any value (since it acts on integer subtraction).
Gerelateerde blogs
T-SQL - Een voortschrijdend totaal over 4 weken maken
Verloren klanten berekenen met T-SQL
Cannot truncate table because it is being referenced by a FOREIGN KEY constraint