How to calculate age (in years) based on Date of Birth and getDate()

select floor((datediff(day,0,@today) - datediff(day,0,@birthdate)) / 365.2425) as age

There are a lot of 365.25 answers here. Remember how leap years are defined:

• Every four years
  • except every 100 years
    • except every 400 years

There are many answers to this question, but I think this one is close to the truth.

The datediff(year,…,…) function, as we all know, only counts the boundaries crossed by the date part, in this case the year. As a result it ignores the rest of the year.

This will only give the age in completed years if the year were to start on the birthday. It probably doesn’t, but we can fake it by adjusting the asking date back by the same amount.

In pseudopseudo code, it’s something like this:

adjusted_today = today - month(dob) + 1 - day(dob) + 1
age = year(adjusted_today - dob)

• The + 1 is to allow for the fact that the month and day numbers start from 1 and not 0.
• The reason we subtract the month and the day separately rather than the day of the year is because February has the annoying tendency to change its length.

The calculation in SQL is:

datediff(year,dob,dateadd(month,-month(dob)+1,dateadd(day,-day(dob)+1,today)))

where dob and today are presumed to be the date of birth and the asking date.

You can test this as follows:

WITH dates AS (
    SELECT
        cast('2022-03-01' as date) AS today,
        cast('1943-02-25' as date) AS dob
)
select
    datediff(year,dob,dateadd(month,-month(dob)+1,dateadd(day,-day(dob)+1,today))) AS age
from dates;

which gives you George Harrison’s age in completed years.

This is much cleaner than fiddling about with quarter days which will generally give you misleading values on the edges.

If you have the luxury of creating a scalar function, you can use something like this:

DROP FUNCTION IF EXISTS age;
GO
CREATE FUNCTION age(@dob date, @today date) RETURNS INT AS
BEGIN
    SET @today = dateadd(month,-month(@dob)+1,@today);
    SET @today = dateadd(day,-day(@dob)+1,@today);
    RETURN datediff(year,@dob,@today);
END;
GO

Remember, you need to call dbo.age() because, well, Microsoft.

After trying MANY methods, this works 100% of the time using the modern MS SQL FORMAT function instead of convert to style 112. Either would work but this is the least code.

Can anyone find a date combination which does not work? I don't think there is one :)

--Set parameters, or choose from table.column instead:

DECLARE @DOB    DATE = '2000/02/29' -- If @DOB is a leap day...
       ,@ToDate DATE = '2018/03/01' --...there birthday in this calculation will be 

--0+ part tells SQL to calc the char(8) as numbers:
SELECT [Age] = (0+ FORMAT(@ToDate,'yyyyMMdd') - FORMAT(@DOB,'yyyyMMdd') ) /10000

What about a solution with only date functions, not math, not worries about leap year

CREATE FUNCTION dbo.getAge(@dt datetime) 
RETURNS int
AS
BEGIN
    RETURN 
        DATEDIFF(yy, @dt, getdate())
        - CASE 
            WHEN 
                MONTH(@dt) > MONTH(GETDATE()) OR 
                (MONTH(@dt) = MONTH(GETDATE()) AND DAY(@dt) > DAY(GETDATE())) 
            THEN 1 
            ELSE 0 
        END
END

declare @birthday as datetime
set @birthday = '2000-01-01'
declare @today as datetime
set @today = GetDate()
select 
    case when ( substring(convert(varchar, @today, 112), 5,4) >= substring(convert(varchar, @birthday, 112), 5,4)  ) then
        (datepart(year,@today) - datepart(year,@birthday))
    else 
        (datepart(year,@today) - datepart(year,@birthday)) - 1
    end

The following script checks the difference in years between now and the given date of birth; the second part checks whether the birthday is already past in the current year; if not, it subtracts it:

SELECT year(NOW()) - year(date_of_birth) - (CONCAT(year(NOW()), '-', month(date_of_birth), '-', day(date_of_birth)) > NOW()) AS Age
FROM tableName;

If you find your age. select (months_between(sysdate,dob)/12) from table_name; If you find your approximate age. select round(months_between(sysdate,dob)/12) from table_name;enter code here There dob is column name. sysdate used for current date . months_between used for find total month dob to currentdate