select CASE WHEN dateadd(year, datediff (year, d1, d2), d1) > d2 THEN datediff (year, d1, d2) - 1 ELSE datediff (year, d1, d2) END as Age
DECLARE @Test TABLE (
EmpName VARCHAR(40) ,
INSERT INTO @Test ( EmpName , BirthDate
VALUES ( '30 Yrs old yesterday' , DATEADD(day , -1 , DATEADD(year , -30 , GETDATE()))
) , ( '30 Yrs old today' , DATEADD(year , -30 , GETDATE())
) , ( '30 Yrs old tomorrow' , DATEADD(day , 1 , DATEADD(year , -30 , GETDATE()))
SELECT EmpName , BirthDate ,
WHEN DATEADD(YY , DATEDIFF(yy , BirthDate , GETDATE()) , BirthDate) < GETDATE()
THEN DATEDIFF(yy , BirthDate , GETDATE())
ELSE DATEDIFF(yy , BirthDate , GETDATE()) - 1
END AS Age
On Demand: No Room for Error: Getting Print Management Right
July 28 @ 12pm ET: Using SQL Server 2016’s In-Memory Technologies
July 28 @ 2pm ET: How to Keep Your Mobile Workforce Secure and Productive
August 4th (first day): Azure Master Class with John Savill
August 11th: Securing Your Data in Office 365 with Nathan O’Bryan
View our new online training catalog
Tweets about @WindowsITPro
To submit an article, please log in and then click Contribute. If you are not a registered user on Windows IT Pro, click Register.
All Sponsored Blogs
Get answers to questions, share tips, and engage with the IT professional community at myITforum.
Are you a data center professional? Join AFCOM for the best data center insights.
Looking to get things done in web development? Hot Scripts offers tens of thousands of scripts you can use.
Database administrator? dBforums offers community insight on everything from ASP to Oracle, and get the latest news from Data Center Knowledge.
Sponsored Introduction Continue on to (or wait seconds) ×