Solved

SQL statement

Posted on 2013-11-07
6
330 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
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 48

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

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 48

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Find unused columns in a table 12 73
Star schema daily updates 2 37
SQL Session Remains Open After ReportViewerControl Form is Closed. 8 34
Need SSIS project 2 28
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

749 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