Create function to calculate age on specific date

 This function will take 2 datetime parameters, the DOB, and a date to check the age at.

CREATE FUNCTION [dbo].[Calc_Age]
(
@DOB datetime , @calcDate datetime
)
RETURNS int
AS
BEGIN
declare @age int
IF (@calcDate < @DOB )
RETURN -1
-- If a DOB is supplied after the comparison date, then return -1
SELECT @age = YEAR(@calcDate) - YEAR(@DOB) +
CASE WHEN DATEADD(year,YEAR(@calcDate) - YEAR(@DOB)
,@DOB) > @calcDate THEN -1 ELSE 0 END
RETURN @age
END

Check the age:

SELECT dbo.Calc_Age('2010-04-13',Getdate())

Output

11

SQL Server DATEADD() Function

The DATEADD() function adds a time/date interval to a date and then returns the date.

Syntax:

DATEADD (datepart , number , datetime_expr)

To add a time measure, the number must be positive. To subtract a time measure, the number must be negative

datepart abbreviations can be used below.

• year, yyyy, yy = Year
• quarter, qq, q = Quarter
• month, mm, m = month
• dayofyear, dy, y = Day of the year
• day, dd, d = Day
• week, ww, wk = Week
• weekday, dw, w = Weekday
• hour, hh = hour
• minute, mi, n = Minute
• second, ss, s = Second
• millisecond, ms = Millisecond

NOTE: DATEADD also accepts abbreviations in the datepart parameter. Use of these abbreviations is generally discouraged as they can be confusing (m vs mi, ww vs w, etc.).

DECLARE @Date DATETIME2 = GETDATE();
SELECT @Date; --2021-05-30 17:01:30.2333333
SELECT DATEADD(YEAR, 1, @Date) --2022-05-30 17:01:30.2333333
SELECT DATEADD(QUARTER, 1, @Date) --2021-08-30 17:01:30.2333333
SELECT DATEADD(WEEK, 1, @Date) --2021-06-06 17:01:30.2333333
SELECT DATEADD(DAY, 1, @Date) --2021-05-31 17:01:30.2333333
SELECT DATEADD(HOUR, 1, @Date) --2021-05-30 18:01:30.2333333
SELECT DATEADD(MINUTE, 1, @Date) --2021-05-30 17:02:30.2333333
SELECT DATEADD(SECOND, 1, @Date) --2021-05-30 17:01:31.2333333
SELECT DATEADD(MILLISECOND, 1, @Date)--2021-05-30 17:01:30.2343333