Solved

# SQL statement

Posted on 2013-11-07
Medium Priority
333 Views
What is a SQL statement that will calculate age using the current date and the birth date?
0
Question by:pstre
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 29

Assisted Solution

Paul Jackson earned 500 total points
ID: 39632550
A good article on calculating age using tsql in the following link :

http://www.wiseowl.co.uk/blog/s216/calculating_age_in_sql_server.htm
0

LVL 49

Assisted Solution

PortletPaul earned 1500 total points
ID: 39632663
This case expression will give you age in years (assuming that's what you want)
``````SELECT
UserID
, YEAR(getdate()) - YEAR(BirthDate)
- ( CASE
WHEN MONTH(BirthDate) > MONTH(getdate())
OR ( MONTH(BirthDate) = MONTH(getdate())
AND DAY(BirthDate) > DAY(getdate())
)
THEN 1
ELSE 0
END ) AS AgeReg
FROM Users
``````
0

LVL 1

Expert Comment

ID: 39632890
select datediff (YY, '1983-03-23', getdate()) as YEAR -- in years

select datediff (MM, '1983-03-23', getdate()) as MONTHS -- in months

select datediff (DD, '1983-03-23', getdate()) as DATES -- in dates
0

LVL 49

Assisted Solution

PortletPaul earned 1500 total points
ID: 39632962
datediff(years...) is not accurate

try

select datediff (YY, '1983-03-08', '2013-11-23')
union all
select datediff (YY, '1983-12-08', '2013-11-23')
union all
select
YEAR('2013-11-23') - YEAR('1983-12-08')
- ( CASE
WHEN MONTH('1983-12-08') > MONTH('2013-11-23')
OR ( MONTH('1983-12-08') = MONTH('2013-11-23')
AND DAY('1983-12-08') > DAY('2013-11-23')
)
THEN 1
ELSE 0
END )

results:
30
30
29

29 is the correct age in years for '1983-12-08' / '2013-11-23'

If the month/day of "today" are earlier than the month/day of the birthdate then 1 needs to be deducted.
0

Author Comment

ID: 39635955
Wow...lots of different answers.  There are 1300 employees where HR needs the age as of 12/31/2013.  I will need to add another column that is "Age as of 12/31/2013.  Having the Birthdate, how can I update the SQL statement below to reflect the age as of 12/31/2013?

select LASTNAME, FRSTNAME, BRTHDATE, DIVISIONCODE_I, DEPRTMNT
from UPR00100
where EMPLCLAS <> 'terminated'
order by EMPLCLAS

Hortin                     Janet                1951-07-15 00:00:00.000      001005       001
Sparks                     Matthew              1976-08-12 00:00:00.000      001008       001
Gammon                     Patricia             1952-08-29 00:00:00.000      003502       003
Porter                     Laura                1967-10-12 00:00:00.000      003001       002
Vaughn                     John                 1971-03-22 00:00:00.000      092002       092
Acheson                    Shawn                1966-03-14 00:00:00.000      003001       002
Tsalik                     Ephraim              1977-02-02 00:00:00.000      001007       001
0

LVL 49

Accepted Solution

PortletPaul earned 1500 total points
ID: 39636374
If your evaluation points are always 31st of December, then the simple year-year approach will work, the case expression approach will also work for that date or any other. I would still recommend the case expression.
``````DECLARE @d AS datetime = '2013-12-31'

SELECT
lastname
, frstname
, divisioncode_i
, deprtmnt
, convert(varchar(10),brthdate,121) AS brthdate
, YEAR(@d) - YEAR(brthdate)
- ( CASE
WHEN MONTH(brthdate) > MONTH(@d)
OR ( MONTH(brthdate) = MONTH(@d)
AND DAY(brthdate) > DAY(@d)
)
THEN 1
ELSE 0
END ) AS Age_by_case

, YEAR(@d) - YEAR(brthdate) AS "Age by year - year"
FROM  UPR00100
;
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff lâ€¦
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month12 days, 23 hours left to enroll