DECLARE @test-2 TABLE (

EmpName VARCHAR(40) ,

BirthDate DATE

);

INSERT INTO @test-2 ( 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 ,

CASE

WHEN DATEADD(YY , DATEDIFF(yy , BirthDate , GETDATE()) , BirthDate) < GETDATE()

THEN DATEDIFF(yy , BirthDate , GETDATE())

ELSE DATEDIFF(yy , BirthDate , GETDATE()) - 1

END AS Age

FROM @test-2;