October 24, 2016 at 4:24 am
Hello,
I am using the code below to generate a list of current ages and the age in 6 months based on the date of birth:
SELECT PERSON_ID,
DOB,
DATEDIFF(YEAR,DOB,GETDATE()) AS [CURRENT_AGE],
year(dateadd(MONTH,6,GETDATE())) - YEAR(DOB) AS [AGE IN SIX MONTHS]
FROM PERSON
However I'm getting some inaccurate results for the Age In Six months, for example one of the rows has a DOB of 1930-06-30 and their current age is calculated as 86, but their age in six months comes back as 87, when it should be still 86.
I've found how to calculate their age in years and months...how can I calculate their age in 6 months with this:
SELECT PERSON_ID, DOB,
DATEDIFF(MONTH,CASE WHEN DAY(DOB) > DAY(GETDATE())THEN DATEADD(MONTH,1,DOB)ELSE DOB END,GETDATE()) / 12 AS Years,
DATEDIFF(MONTH,CASE WHEN DAY(DOB) > DAY(GETDATE())THEN DATEADD(MONTH,1,DOB)ELSE DOB END,GETDATE()) % 12 AS Months
FROM PERSON
Thanks
October 24, 2016 at 4:52 am
this seems to be working so far - but I'm still testing:
datediff(d,dob,dateadd(month,6,getdate())) /365.25
October 24, 2016 at 5:26 am
DATEDIFF will give the difference in the years between two dates, not the actual number of years that have passed between two. So, for example, using DATEDIFF to provide the years between 31-Dec-2015 and 01-Jan-2016 would provide 1, not 0 (as 2016 - 2015 = 1).
This should give you the result you want instead:
CREATE TABLE #PERSON (PERSON_ID INT IDENTITY(1,1),
DOB DATE);
INSERT INTO #PERSON (DOB)
VALUES ('1930-06-30');
SELECT PERSON_ID,
DOB,
DATEDIFF(YEAR,DOB,GETDATE()) AS [CURRENT_AGE],
year(dateadd(MONTH,6,GETDATE())) - YEAR(DOB) AS [AGE IN SIX MONTHS],
--My New Fields
DATEDIFF(YEAR,0, DATEADD(DAY, DATEDIFF(DAY, DOB, GETDATE()),0)) AS NEW_CURRENT_AGE,
DATEDIFF(YEAR,0, DATEADD(MONTH, 6, (DATEADD(DAY, DATEDIFF(DAY, DOB, GETDATE()),0)))) AS NEW_AGE_IN_SIX_MONTHS
FROM #PERSON;
DROP TABLE #PERSON;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 24, 2016 at 5:27 am
AIRWALKER-375999 (10/24/2016)
Hello,I am using the code below to generate a list of current ages and the age in 6 months based on the date of birth:
SELECT PERSON_ID,
DOB,
DATEDIFF(YEAR,DOB,GETDATE()) AS [CURRENT_AGE],
year(dateadd(MONTH,6,GETDATE())) - YEAR(DOB) AS [AGE IN SIX MONTHS]
FROM PERSON
However I'm getting some inaccurate results for the Age In Six months, for example one of the rows has a DOB of 1930-06-30 and their current age is calculated as 86, but their age in six months comes back as 87, when it should be still 86.
I've found how to calculate their age in years and months...how can I calculate their age in 6 months with this:
SELECT PERSON_ID, DOB,
DATEDIFF(MONTH,CASE WHEN DAY(DOB) > DAY(GETDATE())THEN DATEADD(MONTH,1,DOB)ELSE DOB END,GETDATE()) / 12 AS Years,
DATEDIFF(MONTH,CASE WHEN DAY(DOB) > DAY(GETDATE())THEN DATEADD(MONTH,1,DOB)ELSE DOB END,GETDATE()) % 12 AS Months
FROM PERSON
Datediff doesn't work like that. What it does is look at the number of <<datepart>>s crossed by the range between the two dates. Consider this:
SELECT DATEDIFF(YEAR,'20161231 23:59:59','20170101 00:00:00')
That one second crosses a year boundary, and so a value of 1 is returned.
AIRWALKER-375999 (10/24/2016)
this seems to be working so far - but I'm still testing:datediff(d,dob,dateadd(month,6,getdate())) /365.25
This is a better approach, but is prone to much smaller errors - what if you have someone whose DOB is pre-1900? There are a few around... Or what if this code is still running in 2100?
There is a T-SQL answer to this problem - I just haven't worked it out yet. It's not going to be the one-liner you're hoping for, though.
Thomas Rushton
blog: https://thelonedba.wordpress.com
October 24, 2016 at 5:36 am
Thanks guys for the advice and solutions. I'll take into consideration the pre 1900 and post 2100 dob's but I think for right now it's ok.
Thanks again.
October 24, 2016 at 7:45 am
If you are using your above logic, ensure you change your Current DOB logic as well. Otherwise, you'll notice that some other current ages will be wrong (such as 01-Nov-1987, which would show 29, not 28).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 24, 2016 at 8:14 am
If you have the logic for today's age, you have the problem solved. You just need to change the way you approach your solution. In six months, they'll be the same age they would be today if they were born 6 months before. So, instead of changing the formula, just change the DOB.
SELECT PERSON_ID, p.DOB,
DATEDIFF(MONTH,CASE WHEN DAY(p.DOB) > DAY(GETDATE())THEN DATEADD(MONTH,1,p.DOB)ELSE p.DOB END,GETDATE()) / 12 AS Years,
DATEDIFF(MONTH,CASE WHEN DAY(p.DOB) > DAY(GETDATE())THEN DATEADD(MONTH,1,p.DOB)ELSE p.DOB END,GETDATE()) % 12 AS Months,
DATEDIFF(MONTH,CASE WHEN DAY(o.DOB) > DAY(GETDATE())THEN DATEADD(MONTH,1,o.DOB)ELSE o.DOB END,GETDATE()) / 12 AS Years,
DATEDIFF(MONTH,CASE WHEN DAY(o.DOB) > DAY(GETDATE())THEN DATEADD(MONTH,1,o.DOB)ELSE o.DOB END,GETDATE()) % 12 AS Months
FROM PERSON p
CROSS APPLY (SELECT DATEADD(MONTH,-6,DOB)) o(DOB);
October 24, 2016 at 8:36 am
Thom A (10/24/2016)
If you are using your above logic, ensure you change your Current DOB logic as well. Otherwise, you'll notice that some other current ages will be wrong (such as 01-Nov-1987, which would show 29, not 28).
Thanks for mentioning that, my final code is:
select dob,
floor(datediff(d,dob,getdate()) /365.25) as [current_age],
floor(datediff(d,dob,dateadd(month,6,getdate())) /365.25) as [AGE_IN_SIX_MONTHS]
from person
October 24, 2016 at 8:37 am
Luis Cazares (10/24/2016)
If you have the logic for today's age, you have the problem solved. You just need to change the way you approach your solution. In six months, they'll be the same age they would be today if they were born 6 months before. So, instead of changing the formula, just change the DOB.
SELECT PERSON_ID, p.DOB,
DATEDIFF(MONTH,CASE WHEN DAY(p.DOB) > DAY(GETDATE())THEN DATEADD(MONTH,1,p.DOB)ELSE p.DOB END,GETDATE()) / 12 AS Years,
DATEDIFF(MONTH,CASE WHEN DAY(p.DOB) > DAY(GETDATE())THEN DATEADD(MONTH,1,p.DOB)ELSE p.DOB END,GETDATE()) % 12 AS Months,
DATEDIFF(MONTH,CASE WHEN DAY(o.DOB) > DAY(GETDATE())THEN DATEADD(MONTH,1,o.DOB)ELSE o.DOB END,GETDATE()) / 12 AS Years,
DATEDIFF(MONTH,CASE WHEN DAY(o.DOB) > DAY(GETDATE())THEN DATEADD(MONTH,1,o.DOB)ELSE o.DOB END,GETDATE()) % 12 AS Months
FROM PERSON p
CROSS APPLY (SELECT DATEADD(MONTH,-6,DOB)) o(DOB);
Thanks, I didnt think of it like that!
October 24, 2016 at 9:02 am
AIRWALKER-375999 (10/24/2016)
Thom A (10/24/2016)
If you are using your above logic, ensure you change your Current DOB logic as well. Otherwise, you'll notice that some other current ages will be wrong (such as 01-Nov-1987, which would show 29, not 28).Thanks for mentioning that, my final code is:
select dob,
floor(datediff(d,dob,getdate()) /365.25) as [current_age],
floor(datediff(d,dob,dateadd(month,6,getdate())) /365.25) as [AGE_IN_SIX_MONTHS]
from person
These formulas aren't completely reliable.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply