Advertisements

Archive

Posts Tagged ‘Find Age in SQL Server’

How to Calculate Age in Sql Server OR Find age using SQL query

July 3, 2012 2 comments

Calculate age of employee using SQL Server

To calculate an age from the SQL table is little bit tricky.  We are demonstrating how to calculate the age from the date of birth fields in the SQL table. While fill the application form it’s better to fill the Date Of Birth field instead of age field. Because age field can calculate from the Date Of Birth field easily and accurately.

Find the age from the Date Of Birth using SQL Server

Here we are having a table called Employee and having coloumns ID,Name and DOB. From this table we need to find the age of each employee using SQL script.

 

Please see the table structure.

Calculate age from SQL Server

We can use Date Diff function in SQL Server to get the age of each employee. But if we consider date field only it will get wrong answer because it will consider the current year even date of birth not came this year. The below script and result is example.

 

Calculate age from SQL Server

 

For avoiding above mentioned issue, we can use below script to find out the age from the Date Of Birth field using SQL Server.

SELECT DOB AS DateOfBirth,
GETDATE() AS CurrentDate,
DATEDIFF(YEAR,DOB,GETDATE()) - 
(CASE WHEN 
 DATEADD(YY,DATEDIFF(YEAR,DOB,GETDATE()),DOB) > GETDATE()
 THEN 1
 ELSE 0 END) AS Age
FROM Employee
Find the age using SQL Server

Calculate age using SQL Server


		
Advertisements
%d bloggers like this: