SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Working Out Exact Age with DateDiff


Working Out Exact Age with DateDiff

Author
Message
TSQL Tryer
TSQL Tryer
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 708
Hi There,

I am using the following in my SELECT Statement -

DATEDIFF (year,dbo.[CORE_CO-PERSON].[D-O-B],GETDATE())AS Age,

It is pulling me back the age - but it is not exact.

For example I have a person born on 01-08-1977, however his age is being calculated as 32 when it should be 31.

Is there anyway I can get the age to be more exact?

Many Thanks

Ryan
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5796 Visits: 11771
The logic for Age in this select statment shows a simple way to do it.

declare @date_of_birth datetime
set @date_of_birth = '19770801'

select
AGE =
-- Find difference in years and subtract 1 if date is before this years birthday
datediff(yy,@date_of_birth, a.date) -
case when dateadd(yy,datediff(yy, @date_of_birth, a.DATE),@date_of_birth) > a.DATE
then 1 else 0 end,
DATE_OF_BIRTH = @date_of_birth,
a.DATE
from
-- Test data from Date Table Function F_TABLE_DATE
-- Function code available here:
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
F_TABLE_DATE('20090725','20090805') a
order by
a.DATE




Results:
AGE  DATE_OF_BIRTH                  DATE
---- ----------------------- -----------------------
31 1977-08-01 00:00:00.000 2009-07-25 00:00:00.000
31 1977-08-01 00:00:00.000 2009-07-26 00:00:00.000
31 1977-08-01 00:00:00.000 2009-07-27 00:00:00.000
31 1977-08-01 00:00:00.000 2009-07-28 00:00:00.000
31 1977-08-01 00:00:00.000 2009-07-29 00:00:00.000
31 1977-08-01 00:00:00.000 2009-07-30 00:00:00.000
31 1977-08-01 00:00:00.000 2009-07-31 00:00:00.000
32 1977-08-01 00:00:00.000 2009-08-01 00:00:00.000
32 1977-08-01 00:00:00.000 2009-08-02 00:00:00.000
32 1977-08-01 00:00:00.000 2009-08-03 00:00:00.000
32 1977-08-01 00:00:00.000 2009-08-04 00:00:00.000
32 1977-08-01 00:00:00.000 2009-08-05 00:00:00.000

(12 row(s) affected)



Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63188 Visits: 19115
The reason is that you're subtracting years, and as Michael's logic shows, you need to account for where in the current year you are in relation to the date of birth.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
TSQL Tryer
TSQL Tryer
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 708
If below is my currect Select, where would I input your soloution please?

SELECT dbo.[CORE_CO-PERSON].[PERSON-REF]AS Person_Reference,
dbo.[CORE_CO-PERSON].[PERSON-TITLE] AS Person_Title,
dbo.[CORE_CO-PERSON].[INITIALS]AS Initials,
dbo.[CORE_CO-PERSON].[FORENAMES] AS Forenames,
dbo.[CORE_CO-PERSON].[SURNAME]AS Surname,
dbo.[IH_RE-TENANCY].[corr-name1]AS Name_On_Tenancy1,
dbo.[IH_RE-TENANCY].[corr-name2]AS Name_On_Tenancy2,
dbo.[CORE_CO-PERSON].[D-O-B] AS DateOfBirth,
DATEDIFF (year,dbo.[CORE_CO-PERSON].[D-O-B],GETDATE())AS Age,
dbo.[CORE_CO-PERSON].[NHI-NO] AS NationalInsurranceNumber,
dbo.[CORE_CO-PERSON].[ORIGIN-CODE]AS EthnicOrigin,
dbo.[CORE_CO-PERSON].[GENDER]AS Gender,
dbo.[IH_RE-TENANCY].[tenancy-ref]AS Tenancy_Referance,
dbo.[IH_RE-TENANCY].[tncy-start]AS Tenancy_StartDate,
dbo.[IH_RE-TNCY-PERSON].[ON-TNCY] AS On_The_Tenancy

FROM dbo.[IH_RE-TENANCY]
INNER JOIN dbo.[IH_RE-TNCY-PERSON]
ON dbo.[IH_RE-TENANCY].[TNCY-SYS-REF] = dbo.[IH_RE-TNCY-PERSON].[TNCY-SYS-REF]
INNER JOIN dbo.[CORE_CO-PERSON]
ON dbo.[IH_RE-TNCY-PERSON].[PERSON-REF] = dbo.[CORE_CO-PERSON].[PERSON-REF]


WHERE dbo.[IH_RE-TENANCY].[tncy-status] = 'CUR'
AND dbo.[IH_RE-TENANCY].[tenancy-ref] NOT LIKE 'FS%'
AND dbo.[IH_RE-TNCY-PERSON].[END-DATE]IS NULL
--Need to only include NULL records for National Insurrance.
--Only need to pull back those that are 16 and over.
Dave Ballantyne
Dave Ballantyne
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2928 Visits: 8370
Also datediff counts boundaries, not calendars


try this
select datediff(yy,'31dec2009','01jan2010')





Clear Sky SQL
My Blog
Mark Cowne
Mark Cowne
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3121 Visits: 24107
Michael Valentine Jones (7/20/2009)
The logic for Age in this select statment shows a simple way to do it.

declare @date_of_birth datetime
set @date_of_birth = '19770801'

select
AGE =
-- Find difference in years and subtract 1 if date is before this years birthday
datediff(yy,@date_of_birth, a.date) -
case when dateadd(yy,datediff(yy, @date_of_birth, a.DATE),@date_of_birth) > a.DATE
then 1 else 0 end,
DATE_OF_BIRTH = @date_of_birth,
a.DATE
from
-- Test data from Date Table Function F_TABLE_DATE
-- Function code available here:
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
F_TABLE_DATE('20090725','20090805') a
order by
a.DATE




Results:
AGE  DATE_OF_BIRTH                  DATE
---- ----------------------- -----------------------
31 1977-08-01 00:00:00.000 2009-07-25 00:00:00.000
31 1977-08-01 00:00:00.000 2009-07-26 00:00:00.000
31 1977-08-01 00:00:00.000 2009-07-27 00:00:00.000
31 1977-08-01 00:00:00.000 2009-07-28 00:00:00.000
31 1977-08-01 00:00:00.000 2009-07-29 00:00:00.000
31 1977-08-01 00:00:00.000 2009-07-30 00:00:00.000
31 1977-08-01 00:00:00.000 2009-07-31 00:00:00.000
32 1977-08-01 00:00:00.000 2009-08-01 00:00:00.000
32 1977-08-01 00:00:00.000 2009-08-02 00:00:00.000
32 1977-08-01 00:00:00.000 2009-08-03 00:00:00.000
32 1977-08-01 00:00:00.000 2009-08-04 00:00:00.000
32 1977-08-01 00:00:00.000 2009-08-05 00:00:00.000

(12 row(s) affected)





You can also use datepart


AGE =
-- Find difference in years and subtract 1 if date is before this years birthday
datediff(yy,@date_of_birth, a.date) -
case when datepart(dy, @date_of_birth) > datepart(dy, a.DATE)
then 1 else 0 end,



or even this

AGE =
-- Find difference in years and subtract 1 if date is before this years birthday
datediff(yy,@date_of_birth, a.date) -
(sign(datepart(dy, @date_of_birth)-datepart(dy, a.DATE))+1)/2,



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




TSQL Tryer
TSQL Tryer
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 708
AGE =
-- Find difference in years and subtract 1 if date is before this years birthday
datediff(yy,@date_of_birth, a.date) -
case when datepart(dy, @date_of_birth) > datepart(dy, a.DATE)
then 1 else 0 end,


Thank you for all that have replied. I still have no idea how to incorporate the above code into my current select statement (see below)

SELECT dbo.[CORE_CO-PERSON].[PERSON-REF]AS Person_Reference,
dbo.[CORE_CO-PERSON].[PERSON-TITLE] AS Person_Title,
dbo.[CORE_CO-PERSON].[INITIALS]AS Initials,
dbo.[CORE_CO-PERSON].[FORENAMES] AS Forenames,
dbo.[CORE_CO-PERSON].[SURNAME]AS Surname,
dbo.[IH_RE-TENANCY].[corr-name1]AS Name_On_Tenancy1,
dbo.[IH_RE-TENANCY].[corr-name2]AS Name_On_Tenancy2,
dbo.[CORE_CO-PERSON].[D-O-B] AS DateOfBirth,
DATEDIFF (year,dbo.[CORE_CO-PERSON].[D-O-B],GETDATE())AS Age,
dbo.[CORE_CO-PERSON].[NHI-NO] AS NationalInsurranceNumber,
dbo.[CORE_CO-PERSON].[ORIGIN-CODE]AS EthnicOrigin,
dbo.[CORE_CO-PERSON].[GENDER]AS Gender,
dbo.[IH_RE-TENANCY].[tenancy-ref]AS Tenancy_Referance,
dbo.[IH_RE-TENANCY].[tncy-start]AS Tenancy_StartDate,
dbo.[IH_RE-TNCY-PERSON].[ON-TNCY] AS On_The_Tenancy

FROM dbo.[IH_RE-TENANCY]
INNER JOIN dbo.[IH_RE-TNCY-PERSON]
ON dbo.[IH_RE-TENANCY].[TNCY-SYS-REF] = dbo.[IH_RE-TNCY-PERSON].[TNCY-SYS-REF]
INNER JOIN dbo.[CORE_CO-PERSON]
ON dbo.[IH_RE-TNCY-PERSON].[PERSON-REF] = dbo.[CORE_CO-PERSON].[PERSON-REF]


WHERE dbo.[IH_RE-TENANCY].[tncy-status] = 'CUR'
AND dbo.[IH_RE-TENANCY].[tenancy-ref] NOT LIKE 'FS%'
AND dbo.[IH_RE-TNCY-PERSON].[END-DATE]IS NULL
--Need to only include NULL records for National Insurrance.
--Only need to pull back those that are 16 and over.


I'm afraid I am a total novice when it comes to this, so sorry.
Mark Cowne
Mark Cowne
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3121 Visits: 24107
Ryan Keast (7/20/2009)
AGE =
-- Find difference in years and subtract 1 if date is before this years birthday
datediff(yy,@date_of_birth, a.date) -
case when datepart(dy, @date_of_birth) > datepart(dy, a.DATE)
then 1 else 0 end,


Thank you for all that have replied. I still have no idea how to incorporate the above code into my current select statement (see below)

SELECT dbo.[CORE_CO-PERSON].[PERSON-REF]AS Person_Reference,
dbo.[CORE_CO-PERSON].[PERSON-TITLE] AS Person_Title,
dbo.[CORE_CO-PERSON].[INITIALS]AS Initials,
dbo.[CORE_CO-PERSON].[FORENAMES] AS Forenames,
dbo.[CORE_CO-PERSON].[SURNAME]AS Surname,
dbo.[IH_RE-TENANCY].[corr-name1]AS Name_On_Tenancy1,
dbo.[IH_RE-TENANCY].[corr-name2]AS Name_On_Tenancy2,
dbo.[CORE_CO-PERSON].[D-O-B] AS DateOfBirth,
DATEDIFF (year,dbo.[CORE_CO-PERSON].[D-O-B],GETDATE())AS Age,
dbo.[CORE_CO-PERSON].[NHI-NO] AS NationalInsurranceNumber,
dbo.[CORE_CO-PERSON].[ORIGIN-CODE]AS EthnicOrigin,
dbo.[CORE_CO-PERSON].[GENDER]AS Gender,
dbo.[IH_RE-TENANCY].[tenancy-ref]AS Tenancy_Referance,
dbo.[IH_RE-TENANCY].[tncy-start]AS Tenancy_StartDate,
dbo.[IH_RE-TNCY-PERSON].[ON-TNCY] AS On_The_Tenancy

FROM dbo.[IH_RE-TENANCY]
INNER JOIN dbo.[IH_RE-TNCY-PERSON]
ON dbo.[IH_RE-TENANCY].[TNCY-SYS-REF] = dbo.[IH_RE-TNCY-PERSON].[TNCY-SYS-REF]
INNER JOIN dbo.[CORE_CO-PERSON]
ON dbo.[IH_RE-TNCY-PERSON].[PERSON-REF] = dbo.[CORE_CO-PERSON].[PERSON-REF]


WHERE dbo.[IH_RE-TENANCY].[tncy-status] = 'CUR'
AND dbo.[IH_RE-TENANCY].[tenancy-ref] NOT LIKE 'FS%'
AND dbo.[IH_RE-TNCY-PERSON].[END-DATE]IS NULL
--Need to only include NULL records for National Insurrance.
--Only need to pull back those that are 16 and over.


I'm afraid I am a total novice when it comes to this, so sorry.



Add this to the WHERE clause

AND DATEDIFF (year,dbo.[CORE_CO-PERSON].[D-O-B],GETDATE()) -
CASE WHEN DATEPART(dy,dbo.[CORE_CO-PERSON].[D-O-B]) > DATEPART(dy, GETDATE()) THEN 1 ELSE 0 END >= 16



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39684 Visits: 38562
I also need to ask a question, what about leaplings? If some one is born on February 29th, when do you need to consider them a year older, on February 28th or March 1st? Based on this will actually determine how you calculate age.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39684 Visits: 38562
Or


WHERE
...
and [CORE_CO-PERSON].[D-O-B] <= datediff(yy, -16, getdate()) -- where DOB is less than or equal to todays date 16 years ago.



Edit:
After 3 years, someone caught that I had the right idea but the wrong function in the code above. This is what I should have posted here:


WHERE
...
and [CORE_CO-PERSON].[D-O-B] <= dateadd(yy, -16, getdate()) -- where DOB is less than or equal to todays date 16 years ago.



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search