Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Working Out Exact Age with DateDiff Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, July 20, 2009 8:29 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 13, 2013 8:51 AM Points: 163, Visits: 338
 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 ThanksRyan
Post #755813
 Posted Monday, July 20, 2009 8:39 AM
 Hall of Fame Group: General Forum Members Last Login: 2 days ago @ 1:39 PM Points: 3,021, Visits: 10,987
 The logic for Age in this select statment shows a simple way to do it.`declare @date_of_birth datetimeset @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.DATEfrom -- 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') aorder by a.DATE`Results:`AGE DATE_OF_BIRTH DATE---- ----------------------- -----------------------31 1977-08-01 00:00:00.000 2009-07-25 00:00:00.00031 1977-08-01 00:00:00.000 2009-07-26 00:00:00.00031 1977-08-01 00:00:00.000 2009-07-27 00:00:00.00031 1977-08-01 00:00:00.000 2009-07-28 00:00:00.00031 1977-08-01 00:00:00.000 2009-07-29 00:00:00.00031 1977-08-01 00:00:00.000 2009-07-30 00:00:00.00031 1977-08-01 00:00:00.000 2009-07-31 00:00:00.00032 1977-08-01 00:00:00.000 2009-08-01 00:00:00.00032 1977-08-01 00:00:00.000 2009-08-02 00:00:00.00032 1977-08-01 00:00:00.000 2009-08-03 00:00:00.00032 1977-08-01 00:00:00.000 2009-08-04 00:00:00.00032 1977-08-01 00:00:00.000 2009-08-05 00:00:00.000(12 row(s) affected)`
Post #755829
 Posted Monday, July 20, 2009 8:49 AM
 SSC-Dedicated Group: Administrators Last Login: Today @ 10:12 AM Points: 32,273, Visits: 14,526
 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: @way0utwestForum Etiquette: How to post data/code on a forum to get the best help
Post #755849
 Posted Monday, July 20, 2009 8:53 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 13, 2013 8:51 AM Points: 163, Visits: 338
 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_TenancyFROM 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.
Post #755857
 Posted Monday, July 20, 2009 8:54 AM
 SSCommitted Group: General Forum Members Last Login: Sunday, December 01, 2013 11:17 PM Points: 1,949, Visits: 8,264
 Also datediff counts boundaries, not calendarstry this`select datediff(yy,'31dec2009','01jan2010')`
Post #755859
 Posted Monday, July 20, 2009 9:03 AM
 SSCommitted Group: General Forum Members Last Login: Today @ 8:25 AM Points: 1,694, Visits: 19,540
 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 datetimeset @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.DATEfrom -- 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') aorder by a.DATE`Results:`AGE DATE_OF_BIRTH DATE---- ----------------------- -----------------------31 1977-08-01 00:00:00.000 2009-07-25 00:00:00.00031 1977-08-01 00:00:00.000 2009-07-26 00:00:00.00031 1977-08-01 00:00:00.000 2009-07-27 00:00:00.00031 1977-08-01 00:00:00.000 2009-07-28 00:00:00.00031 1977-08-01 00:00:00.000 2009-07-29 00:00:00.00031 1977-08-01 00:00:00.000 2009-07-30 00:00:00.00031 1977-08-01 00:00:00.000 2009-07-31 00:00:00.00032 1977-08-01 00:00:00.000 2009-08-01 00:00:00.00032 1977-08-01 00:00:00.000 2009-08-02 00:00:00.00032 1977-08-01 00:00:00.000 2009-08-03 00:00:00.00032 1977-08-01 00:00:00.000 2009-08-04 00:00:00.00032 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,` ____________________________________________________How to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #755870
 Posted Monday, July 20, 2009 9:23 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 13, 2013 8:51 AM Points: 163, Visits: 338
 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_TenancyFROM 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.
Post #755911
 Posted Monday, July 20, 2009 9:27 AM
 SSCommitted Group: General Forum Members Last Login: Today @ 8:25 AM Points: 1,694, Visits: 19,540
 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_TenancyFROM 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` ____________________________________________________How to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #755917
 Posted Monday, July 20, 2009 9:42 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 5:08 PM Points: 22,083, Visits: 28,985
 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.
Post #755933
 Posted Monday, July 20, 2009 9:49 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 5:08 PM Points: 22,083, Visits: 28,985
 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.`
Post #755944

 Permissions