I need a query that will pull all children with a birth date from two years ago through yesterday. So using today as a for instance the query would return everyone with BirthDT from 1/1/2018 to 3/4/2020. For the remainder of 2020 the Start Date will always be 1/1/2018 and End Date will vary depending on the date the query is executed. Next year it will need to use 1/1/2019 as the Start Date.
Anything like this of course just give me a rolling two year window.
WHERE BirthDT >
DATEADD(yy, -2, CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)))
Advertisement
Answer
You can use DATEFROMPARTS function to create 1.1. of any year and YEAR() to get current year.
For end date, to avoid today’s births just cast GETDATE() to DATE
WHERE BirthDT >= DATEFROMPARTS(YEAR(GETDATE())-2,1,1) AND BirthDT < CAST(GETDATE() AS DATE)
For datetime fields, it always best to use >= on the left side which will include 0:00 on the starting date, and < on the right side with next day, to get complete data from previous day, if time part is used.
In this case this will include 1.1.2018 0:00:00 and not include 5.3.2020 0:00, but include 4.3.2020 23:59:59