How to create a rolling date search between two months

Transact-SQL
1

Hello, I currently have a query pulling data back 1 month from the current date as written below:

where a.sandbox BETWEEN DATEADD(month, -1, GETDATE () ) AND GETDATE ()

However, say I wanted to pull data only between months 3 and 6. Is this possible?

where a.sandbox BETWEEN DATEADD (month, -3, AND month, -6 GETDATE () ) AND GETDATE ()

something along these lines? Thank you so much

2

have you tried

where a.sandbox BETWEEN DATEADD (month, -6, GETDATE ()) AND DATEADD (month, -3, GETDATE ())

3

There is an inherent bug in the way you have this constructed. GETDATE() returns the date and time at the time it is execute. If your query is run at 8am today - it will return data from one month ago from today at 8am.

If the goal is to return data from the beginning of the day - one month ago from today, excluding today:

SELECT ...
  FROM ...
 WHERE a.sandbox >= DATEADD(month, -1, CAST(GETDATE() AS DATE))
   AND a.sandbox <  CAST(GETDATE() AS DATE)

If you want variable months back - then we can use a variables to define the start/end dates:

DECLARE @startDate datetime = DATEADD(month, -6, CAST(GETDATE() AS DATE))  -- 6 months ago from today
      , @endDate datetime = DATEADD(month, -3, CAST(GETDATE() AS DATE))  -- 3 months ago from today

SELECT ...
  FROM ...
 WHERE a.sandbox >= @startDate
   AND a.sandbox <  @endDate;

If you want to include today (or for prior months the day selected):

DECLARE @startDate datetime = DATEADD(month, -6, CAST(GETDATE() AS DATE))  -- 6 months ago from today
      , @endDate datetime = DATEADD(month, -3, CAST(GETDATE() AS DATE))  -- 3 months ago from today

SELECT ...
  FROM ...
 WHERE a.sandbox >= @startDate
   AND a.sandbox <  DATEADD(day, 1, @endDate);

Notice the use of less than here - what that is doing is including everything up to but not including that value. The value used here will be: YYYY-MM-DD 00:00:00.000

Which will include everything up to that date but not including that date.