Solved

SQL statement

Posted on 2013-11-07
6
331 Views
Last Modified: 2013-11-14
What is a SQL statement that will calculate age using the current date and the birth date?
0
Comment
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
  • Learn & ask questions
6 Comments
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 125 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

by:PortletPaul
PortletPaul earned 375 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

Open in new window

0
 
LVL 1

Expert Comment

by:mkgtvm
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 375 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

by:pstre
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

by:
PortletPaul earned 375 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
;

Open in new window

0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question