SQL statement

What is a SQL statement that will calculate age using the current date and the birth date?
pstreAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
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
;

Open in new window

0
 
Paul JacksonConnect With a Mentor Software EngineerCommented:
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
 
PortletPaulConnect With a Mentor freelancerCommented:
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

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mkgtvmCommented:
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
 
PortletPaulConnect With a Mentor freelancerCommented:
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
 
pstreAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.