WHERE date is between [the beginning of the month that was 6 months ago] and now

Question

I use the DATEADD function a lot when working with date ranges, especially if I'm working with rolling time periods. If I wanted to see all results from the past 6 months, I'd of course use...

WHERE [DateField] BETWEEN DATEADD(mm,-6,GETDATE()) AND GETDATE()

...and that would show me everything from July 20, 2022 to today, January 20, 2023.

But what If I wanted to see everything from the beginning of July? What if I wanted to see everything from the beginning of whatever month was 6 months ago, or whatever time period I specify, without having to manually plug in dates?

I tried the following...

WHERE [DateField] BETWEEN DATEADD(mm,-6,(DATEPART(m,GETDATE()))) AND GETDATE()

...in an attempt to get it to focus on the month, but for some reason now I'm suddenly getting all the results from this table I'm working with, with data going back to 2014. I think I'm on the right track in terms of logic, but something's obviously not quite right.

Answer 1

The Why

A good troubleshooting step is to see the value your date function is returning. For example, you can execute this SELECT statement:

SELECT DATEADD(mm,-6,(DATEPART(m,GETDATE())))

This returns a date of 1899-07-02 00:00:00:00 which effectively turns your WHERE clause into:

WHERE [DateField] BETWEEN '1899-07-02' AND '2023-01-20

Thus explaining why your query returned all the records in the table.

Solution

First, you can truncate the current date to the first day of the current month:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS FOM;

Output: 2023-01-01 00:00:00

Now add another DATEADD function to subtract six months:

SELECT DATEADD(MONTH, -6, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS FOM_SIX_MNTHS_AGO;

Output: 2022-07-01 00:00:00

Putting it all together:

SELECT * 
FROM [TABLE_NAME] 
WHERE [DateField] BETWEEN DATEADD(MONTH, -6, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AND GETDATE();