009. Calculating Age from DOB

 

Using T-SQL to calculate age from date of birth.

 

 

If I have a table full of birth dates, and want to calculate their ages. This is the sure way to do it.
I have tried doing it other ways, and it has been fraught with errors that creep in with things like leap years, months with different numbers of days, etc.
But once, I was lucky enough to work with a good SQL Guru, and he taught me this.

Create Table [dbo].[tblNameDOB](
[ID] [int] Null,
[Name] [nvarchar](50) Null,
[BirthDate] Datetime
) On [Primary]

Insert [tblNameDOB] (ID, [Name], BirthDate) Values (1, ‘Albert’, ‘1976-04-07’)
Insert [tblNameDOB] (ID, [Name], BirthDate) Values (1, ‘Henry’, ‘1990-12-11’)
Insert [tblNameDOB] (ID, [Name], BirthDate) Values (1, ‘Maddison’, ‘2002-6-23’)

Select
*
,DateDiff(YY, I.BirthDate, GetDate())
–
Case
When(
(
Month(I.BirthDate)*100
+
Day(I.BirthDate)
)
>
(
Month(GetDate())*100
+
Day(GetDate())
)
)
Then 1 Else 0
End As [Age]
From
[tblNameDOB] As I