February 25, 2019 at 9:25 am
ScottPletcher - Monday, February 25, 2019 7:58 AMbelow86 - 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 "<=".
I did see you used just less than. My point is that if you catch them when they first turn 12 then they will always be included until they turn 66.
Using today's date(02/25/2019), your logic calculates 02/25/2006. If someone turned 12 today they would not be included because their birthday was 02/25/2007.
If you stick with the less than then you need to subtract 12 but add a day, to get 02/26/2007. Those people who are 12 but not 13 yet, their birthdays would still be less than 02/26/2007. Or less than equal to 02/25/2007. Someone who is 12 1/2 today would have been born on 08/25/2006, they would be included on my logic, but not with yours.
OP said: "So, I am trying to write a query that will find everyone age 12 - 65 in a table."
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
February 25, 2019 at 10:13 am
below86 - Monday, February 25, 2019 9:25 AMScottPletcher - Monday, February 25, 2019 7:58 AMbelow86 - 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 "<=".
I did see you used just less than. My point is that if you catch them when they first turn 12 then they will always be included until they turn 66.
Using today's date(02/25/2019), your logic calculates 02/25/2006. If someone turned 12 today they would not be included because their birthday was 02/25/2007.
If you stick with the less than then you need to subtract 12 but add a day, to get 02/26/2007. Those people who are 12 but not 13 yet, their birthdays would still be less than 02/26/2007. Or less than equal to 02/25/2007. Someone who is 12 1/2 today would have been born on 08/25/2006, they would be included on my logic, but not with yours.
OP said: "So, I am trying to write a query that will find everyone age 12 - 65 in a table."
Yes, I think I should have used -12 there not -13. But I know that "<=" the exact birth date will never be correct, for any age, because it includes at least one day that should not be there.
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 25, 2019 at 11:02 am
ScottPletcher - Monday, February 25, 2019 10:13 AMbelow86 - Monday, February 25, 2019 9:25 AMScottPletcher - Monday, February 25, 2019 7:58 AMbelow86 - 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 "<=".
I did see you used just less than. My point is that if you catch them when they first turn 12 then they will always be included until they turn 66.
Using today's date(02/25/2019), your logic calculates 02/25/2006. If someone turned 12 today they would not be included because their birthday was 02/25/2007.
If you stick with the less than then you need to subtract 12 but add a day, to get 02/26/2007. Those people who are 12 but not 13 yet, their birthdays would still be less than 02/26/2007. Or less than equal to 02/25/2007. Someone who is 12 1/2 today would have been born on 08/25/2006, they would be included on my logic, but not with yours.
OP said: "So, I am trying to write a query that will find everyone age 12 - 65 in a table."Yes, I think I should have used -12 there not -13. But I know that "<=" the exact birth date will never be correct, for any age, because it includes at least one day that should not be there.
What am I missing? If the Birthday field is just a Date and no time how "will never be correct' using '<='?
If your 12th birthday is 02/25/2019, you are included.
If your 12th birthday was 02/24/2019, you are included.
If your 12th birthday is 02/26/2019, you are NOT included.
What day shouldn't be there?WHERE Birthdate > 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.
Viewing 3 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply