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
Jan 19: Deploying Windows 10 OS using Microsoft Deployment Toolkit with Mikael Nyström
Jan 24: Hyper Convergence 3.0 with Alan Sugano
Jan 25: Crunching Big Data with Apache Spark with Sasha Goldshtein
Jan 26: vRealize Automation (vRA)
Jan 26: The Past, Present and Future of Patching of Windows with Susan Bradley
Feb 2: PowerShell Master Class with John Savill
Feb 7: Mastering Windows 10 v1607 in the Enterprise with Johan Arwidmark
Feb 28: Navigating the Capital Raise Pathway in a Complex IT Landscape with Theresa Caragol
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) ×