SQL Server - get a person's age from the current date and birth date

This is a little more complicate than you might think. Simply using DATEDIFF won't work correctly, because it only subtracts the 'year' portion of the date. It doesn't take into account if the month/day is before or after the current date. 

I've come across a formula that works, and it's not too complicated. See below. 

Related Articles

... and you 'll find more on the SQL (General) Menu

 

declare @now date = '2/23/2024'
 
DECLARE @dates TABLE (BRTHDATE date)
INSERT INTO @dates(BRTHDATE) VALUES ('1/1/2023')
INSERT INTO @dates(BRTHDATE) VALUES ('2/1/2023')
INSERT INTO @dates(BRTHDATE) VALUES ('3/1/2023')
INSERT INTO @dates(BRTHDATE) VALUES ('4/1/2023')
INSERT INTO @dates(BRTHDATE) VALUES ('5/1/2023')
INSERT INTO @dates(BRTHDATE) VALUES ('6/1/2023')
INSERT INTO @dates(BRTHDATE) VALUES ('7/1/2023')
INSERT INTO @dates(BRTHDATE) VALUES ('8/1/2023')
INSERT INTO @dates(BRTHDATE) VALUES ('9/1/2023')
INSERT INTO @dates(BRTHDATE) VALUES ('10/1/2023')
INSERT INTO @dates(BRTHDATE) VALUES ('11/1/2023')
INSERT INTO @dates(BRTHDATE) VALUES ('12/1/2023')
 
SELECT e.BRTHDATE,
        --simply subtracts the year part of the date. Always the same number. Doesn't take into account if the month/date is before or after the current day
        DATEDIFF(YEAR, e.BRTHDATE,@now) AS birthdate1,
        --converts both dates to characters. The first row is 20240223 and 20230101
        --subtracts: 20240223 - 20230101 = 10122
        --divide: 10122 / 10000 = 1
        (CONVERT(int,CONVERT(char(8),@Now,112))-CONVERT(char(8),e.BRTHDATE,112))/10000 AS birthdate2
    FROM @dates e   
     
     
    

 

 

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences