Calculate Age From Birthdate Column (T-SQL)


You can have a few options to calculate age in years from a date of birth column. When the table gets bigger, the performance can be an issue.
Most important thing you should do is to make sure that you have appropriate indexes in place.
The following scripts have the query to retrieve age from the table and the two indexes I have created on the table.



USE myDB
GO
--CREATE NONCLUSTERED INDEX idx_Birthday
--ON [dbo].[myTable] ([birthdate])
--GO

 
--CREATE NONCLUSTERED INDEX idx_Birthday_myTableid
--ON [dbo].[myTable] ([birthdate])
--INCLUDE ([myTableid])
--GO

 

--Inline 1
SELECT [birthdate]
  , DATEDIFF(yy, [birthdate], current_timestamp) - CASE WHEN
(MONTH([birthdate]) > MONTH(current_timestamp)) OR (MONTH([birthdate]) = MONTH(current_timestamp)
AND DAY([birthdate]) > DAY(current_timestamp)) THEN 1 ELSE 0 END
  FROM  [dbo].[myTable]
  WHERE [birthdate] is not null
--order by myTableid

--Inline 2
  SELECT myTableid, [birthdate]
  , CASE WHEN
 (DATEADD(year,DATEDIFF(year, [birthdate]  ,current_timestamp) , [birthdate]) > current_timestamp)
THEN DATEDIFF(year, [birthdate]  ,current_timestamp) -1
ELSE DATEDIFF(year, [birthdate]  ,current_timestamp) END
  FROM  [dbo].[myTable]
  WHERE [birthdate] is not null 
 --order by myTableid

 --Inline 3
 SELECT  [birthdate],FLOOR((CAST (current_timestamp AS INT) - CAST([birthdate] AS INT)) / 365.25) AS Age
  FROM  [dbo].[myTable]
  WHERE [birthdate] is not null
--order by myTableid


--Scalar UDF
  Select  [birthdate], [dbo].[udfGetAge]([birthdate])    
  FROM  [dbo].[myTable]
  WHERE [birthdate] is not null
  --order by myTableid


--Computed Column
--Cannot set Persisted because current_timestamp is not  deterministic
  Select  [birthdate], Age   
  FROM  [dbo].[myTable]
  WHERE [birthdate] is not null


  

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s