-- RETURNS INCORRECT RESULTS, SO DON'T USE IT SELECT DATEDIFF(year, date_of_birth, CURRENT_TIMESTAMP) AS Age FROM my_table
-- RETURNS INCORRECT RESULTS, SO DON'T USE IT SELECT FLOOR(DATEDIFF(day, date_of_birth, CURRENT_TIMESTAMP) / 365.25) AS Age FROM my_table
-- Most compact solution SELECT (0+CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)-CONVERT(CHAR(8),date_of_birth,112))/10000 AS Age FROM my_table
-- Fastest solution 1 SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(date_of_birth) - (CASE WHEN (MONTH(date_of_birth) > MONTH(CURRENT_TIMESTAMP)) OR (MONTH(date_of_birth) = MONTH(CURRENT_TIMESTAMP) AND DAY(date_of_birth) > DAY(CURRENT_TIMESTAMP)) THEN 1 ELSE 0 END) AS Age FROM my_table -- Fastest solution 2 SELECT DATEDIFF(year, date_of_birth, CURRENT_TIMESTAMP) - CASE WHEN DATEADD(year, DATEDIFF(year,date_of_birth,CURRENT_TIMESTAMP), date_of_birth) > CURRENT_TIMESTAMP THEN 1 ELSE 0 END AS Age FROM my_table
-- suboptimal solutions SELECT DATEDIFF(yy, date_of_birth, CURRENT_TIMESTAMP) - CASE WHEN (MONTH(CURRENT_TIMESTAMP) * 100 + DAY(CURRENT_TIMESTAMP)) < (MONTH(date_of_birth) * 100 + DAY(date_of_birth)) THEN 1 ELSE 0 END AS Age FROM my_table SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(date_of_birth) - CASE WHEN MONTH(CURRENT_TIMESTAMP) < MONTH(date_of_birth) THEN 1 WHEN MONTH(CURRENT_TIMESTAMP) > MONTH(date_of_birth) THEN 0 WHEN DAY(CURRENT_TIMESTAMP) < DAY(date_of_birth) THEN 1 ELSE 0 END AS Age FROM my_table SELECT CASE WHEN (MONTH(CURRENT_TIMESTAMP)*100)+DAY(CURRENT_TIMESTAMP) >= (MONTH(date_of_birth)*100)+DAY(date_of_birth) THEN DATEDIFF(Year,date_of_birth,CURRENT_TIMESTAMP) ELSE DATEDIFF(Year,date_of_birth,CURRENT_TIMESTAMP) - 1 END AS Age FROM my_table
-- Scalar UDF CREATE FUNCTION dbo.Age(@dob datetime) RETURNS int WITH SCHEMABINDING AS Begin Declare @age int Set @age=(SELECT DATEDIFF(year, @dob, CURRENT_TIMESTAMP)) If DATEADD(year,@age,@dob) > CURRENT_TIMESTAMP Set @age=@age-1 Return @age End SELECT dbo.Age(date_of_birth) FROM my_table
Back to SQL Server main menu. Mail your comments to gertjans@xs4all.nl.