SQL date interval every 6 months

My SQL Server 2014 table includes a datetime column called CreatedOn.

Every day I need to idenfiy companies that were created on this date exactly 6 months ago or a multiple of 6 months ago (i.e 6 months after the stored datetime, 12 months after, 18 months after, etc). I can write the rest of the query but cannot figure out how to identify the rows that will match the list of dates (i.e Oct 27th 2020, Apr 27th 2020, Oct 27th 2019, Apr 27th 2019, Oct 27th 2018, Apr 27th 2018, etc).

Here is the query to get the companies that were created exactly 6 months ago. How do I adapt the query to include the additional 6 month intervals?

select * 
from work 
where DATEPART(d, CreatedOn) = DATEPART(d, DATEADD(m, -6, getdate()))
  and DATEPART(m, CreatedOn) = DATEPART(m, DATEADD(m, -6, getdate()))
  and DATEPART(yyyy, CreatedOn) = DATEPART(yyyy, DATEADD(m, -6, getdate()))

Sample data

Name          CreatedOn
-------------------------------------
Company A     2020-10-27 13:49:15.597
Company B     2021-04-27 15:25:09.720
Company C     2021-03-16 15:50:01.443
Company D     2018-04-27 21:58:18.903

Data Table

I would want Company A and Company D in my results set

Answer

I was overthinking it. I don’t really care about the year and the day of the month will always be the current day (running just before midnight), I just need to get the current month and the 6 months ago month.

select * from work where DATEPART(d, CreatedOn) = DATEPART(d, getdate()) AND (DATEPART(m, CreatedOn) = DATEPART(m, DATEADD(m, -6, getdate())) or DATEPART(m, CreatedOn) = DATEPART(m, getdate()))

Thanks for everybody’s help.

Advertisement

Answer