February 21, 2019 at 9:18 pm
My first post here so I don't know how people tend to respond. I searched the forums for "Age Range From Today's Date". Here are the results from that search. I admit that I didn't read every post in it's entirety but I did skim each and didn't see an obvious answer so thought I'd just ask my question. I realize there are multiple ways of phrasing something so please forgive me if you think of a better way to ask the question, it's been a long day already.
So, I am trying to write a query that will find everyone age 12 - 65 in a table. I need this query to automatically update whenever the resulting report is run so I obviously can't use a hard-coded date. Given today's date of 02/21/2019, I can hard code my query as:
select * from PatientProfile
where Birthdate < '2002-02-21'
this tells me that there are exactly 100 (nice, eh?) people who are older than 12. If I change the query to:
select * from PatientProfile
where Sex like 'f'
and Birthdate between '1954-02-21' and '2002-02-21'
my results reduce to 82. But of course, if I run either of these queries in a week or a month it could change as someone (or more than one) may have turned 12 or 66 thus joining or leaving the group. I assume I'll have to use getdate() at some point but I'm at a loss as to how to get these results. Any help would be appreciated.
February 21, 2019 at 9:45 pm
tech.simmons - Thursday, February 21, 2019 9:18 PMMy first post here so I don't know how people tend to respond. I searched the forums for "Age Range From Today's Date". Here are the results from that search. I admit that I didn't read every post in it's entirety but I did skim each and didn't see an obvious answer so thought I'd just ask my question. I realize there are multiple ways of phrasing something so please forgive me if you think of a better way to ask the question, it's been a long day already.So, I am trying to write a query that will find everyone age 12 - 65 in a table. I need this query to automatically update whenever the resulting report is run so I obviously can't use a hard-coded date. Given today's date of 02/21/2019, I can hard code my query as:
select * from PatientProfile
where Birthdate < '2002-02-21'this tells me that there are exactly 100 (nice, eh?) people who are older than 12. If I change the query to:
select * from PatientProfile
where Sex like 'f'
and Birthdate between '1954-02-21' and '2002-02-21'my results reduce to 82. But of course, if I run either of these queries in a week or a month it could change as someone (or more than one) may have turned 12 or 66 thus joining or leaving the group. I assume I'll have to use getdate() at some point but I'm at a loss as to how to get these results. Any help would be appreciated.
Create a function to calculate the ageCREATE FUNCTION dbo.CalcAge (
@DateOfBirth datetime
, @CurrentDate datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT Age = DATEDIFF(yy,@DateOfBirth,@CurrentDate) -
CASE WHEN @CurrentDate < DATEADD(YY,DATEDIFF(YY,@DateOfBirth,@CurrentDate), @DateOfBirth) THEN 1 ELSE 0 END;
Then call the functionSELECT a.Age
FROM dbo.CalcAge('1969-02-28', GETDATE()) AS a;
SELECT *, a.Age
FROM SomeTable AS t
CROSS APPLY dbo.CalcAge(t.DOB, GETDATE()) AS a
WHERE a.Age BETWEEN 12 AND 65
February 22, 2019 at 8:38 am
Thank you. I appreciate that. If you have time, can you explain some of what that is doing at different points? I don't mind copying, pasting and editing as needed but I also like to know what is happening when, where, and why.
February 22, 2019 at 9:03 am
As in your original method, you don't need to calc everyone's age, just check the correct date range. Like this:
WHERE Birthdate >= DATEADD(DAY, 1, DATEADD(YEAR, -66, CAST(GETDATE() AS date))) AND
Birthdate < DATEADDY(YEAR, -13, CAST(GETDATE() AS date))
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 22, 2019 at 10:32 am
Wouldn't it be more like this?
WHERE Birthdate >= DATEADD(YY, -65, CAST(GETDATE() AS DATE))
AND Birthdate <= DATEADD(YY, -12, CAST(GETDATE() AS DATE))
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
February 22, 2019 at 10:39 am
below86 - Friday, February 22, 2019 10:32 AMWouldn't it be more like this?
WHERE Birthdate >= DATEADD(YY, -65, CAST(GETDATE() AS DATE))
AND Birthdate <= DATEADD(YY, -12, CAST(GETDATE() AS DATE))
Have you checked the actual dates produced by the code and the corresponding ages?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 22, 2019 at 12:01 pm
I will play with these possibilities a bit later. I ended up back here to research something unrelated and that has my time/focus at the moment. Thank you all for the input.
February 22, 2019 at 12:04 pm
ScottPletcher - Friday, February 22, 2019 10:39 AMbelow86 - Friday, February 22, 2019 10:32 AMWouldn't it be more like this?
WHERE Birthdate >= DATEADD(YY, -65, CAST(GETDATE() AS DATE))
AND Birthdate <= DATEADD(YY, -12, CAST(GETDATE() AS DATE))Have you checked the actual dates produced by the code and the corresponding ages?
Yes, ran today(02/22/2019) the first gives me 02/22/1954, today would be that persons 65 birthday, so they should be included.
The second date returned is 02/22/2007, today would be there 12th birthday, so should be included.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
February 22, 2019 at 12:10 pm
below86 - Friday, February 22, 2019 12:04 PMScottPletcher - Friday, February 22, 2019 10:39 AMbelow86 - Friday, February 22, 2019 10:32 AMWouldn't it be more like this?
WHERE Birthdate >= DATEADD(YY, -65, CAST(GETDATE() AS DATE))
AND Birthdate <= DATEADD(YY, -12, CAST(GETDATE() AS DATE))Have you checked the actual dates produced by the code and the corresponding ages?
Yes, ran today(02/22/2019) the first gives me 02/22/1954, today would be that persons 65 birthday, so they should be included.
The second date returned is 02/22/2007, today would be there 12th birthday, so should be included.
OK, after taking more than a second to think about it, you are correct. Sorry. :crazy:
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
February 22, 2019 at 12:14 pm
below86 - Friday, February 22, 2019 12:04 PMScottPletcher - Friday, February 22, 2019 10:39 AMbelow86 - Friday, February 22, 2019 10:32 AMWouldn't it be more like this?
WHERE Birthdate >= DATEADD(YY, -65, CAST(GETDATE() AS DATE))
AND Birthdate <= DATEADD(YY, -12, CAST(GETDATE() AS DATE))Have you checked the actual dates produced by the code and the corresponding ages?
Yes, ran today(02/22/2019) the first gives me 02/22/1954, today would be that persons 65 birthday, so they should be included.
The second date returned is 02/22/2007, today would be there 12th birthday, so should be included.
Hmm, wouldn't someone born on 12-22-1953 also be 65 today?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 22, 2019 at 1:52 pm
The issue with using simple DATEADD or DATEDIFF is that they count Year Boundaries. In other words, they count the number of times that the year has changed. The following code produces a result of 1 year, even though the date difference is actually 3 days.SELECT NumYears = DATEDIFF(year, '2018-12-30', '2019-01-02')
Take a look at Lynn's article on Calculating Age
February 22, 2019 at 1:53 pm
tech.simmons - Friday, February 22, 2019 8:38 AMThank you. I appreciate that. If you have time, can you explain some of what that is doing at different points? I don't mind copying, pasting and editing as needed but I also like to know what is happening when, where, and why.
High-lite the parts that need explanation, and I will try to explain as best I can
February 22, 2019 at 2:08 pm
DesNorton - Friday, February 22, 2019 1:52 PMThe issue with using simple DATEADD or DATEDIFF is that they count Year Boundaries. In other words, they count the number of times that the year has changed. The following code produces a result of 1 year, even though the date difference is actually 3 days.SELECT NumYears = DATEDIFF(year, '2018-12-30', '2019-01-02')Take a look at Lynn's article on Calculating Age
No, the problem lies solely with DATEDIFF. DATEADD uses whole periods. DATEADD only appears to have a problem, because people use DATEDIFF to calculate inputs for DATEADD.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 25, 2019 at 7:35 am
ScottPletcher - Friday, February 22, 2019 12:14 PMHmm, wouldn't someone born on 12-22-1953 also be 65 today?
You are correct on the date to include anyone 65. I still don't understand why you would subtract 13 to include those 12 and over. I think this would be the correct code to use.WHERE Birthdate >= DATEADD(DAY, 1, DATEADD(YEAR, -66, CAST(GETDATE() AS date)))
AND Birthdate <= DATEADD(YY, -12, CAST(GETDATE() AS DATE))
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
February 25, 2019 at 7:58 am
below86 - Monday, February 25, 2019 7:35 AMYou are correct on the date to include anyone 65. I still don't understand why you would subtract 13 to include those 12 and over. I think this would be the correct code to use.WHERE Birthdate >= DATEADD(DAY, 1, DATEADD(YEAR, -66, CAST(GETDATE() AS date)))
AND Birthdate <= DATEADD(YY, -12, CAST(GETDATE() AS DATE))
For the same reason I had to adjust the first birthday. You have to include everyone who's not yet 13, not just those who are exactly 12. Note that the condition I used for the 12th birthday date was "<" not "<=".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply