You need to create a user function first
create function [dbo].[fn_GetAge]
(@dob AS datetime,@currentDate as datetime)
returns int
AS
BEGIN
DECLARE @age int
IF cast(datepart(m,@currentDate) as int) > cast(datepart(m,@dob) as int)
SET @age = cast(datediff(yyyy,@dob, @currentDate) as int)
else
IF cast(datepart(m,@currentDate) as int) = cast(datepart(m,@dob) as int)
IF datepart(d,@currentDate) >= datepart(d,@dob)
SET @age = cast(datediff(yyyy,@dob, @currentDate) as int)
ELSE
SET @age = cast(datediff(yyyy,@dob, @currentDate) as int) -1
ELSE
SET @age = cast(datediff(yyyy,@dob, @currentDate) as int) - 1
RETURN @age
END
Once the user function has been created, use it like the one mentioned below
SELECT ID, NAME, DOB, fn_GetAge(DOB, GetDate()) AS AGE FROM TABLE
You can put the result in a temporary table to get the desired output.
Hope this solution helps