4

I need help selecting data where the date is in the previous 3 months. (note: I'm not asking for the last 3 months i.e. DATEADD(MONTH, -3, GETDATE()) )

The reason I don't want to use the DATEADD method is that this query could be run in the middle of the month and I don't want it to return data leading up til that point

A.Cassin
  • 45
  • 1
  • 2
  • 6

1 Answers1

5

You can use this methodology to determine the first day of 3 months ago, and the last day of the previous month:

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0)   --First day of 3 months ago
select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) --Last Day of previous month

Then, just use it on your where clause.

declare @start date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0)
declare @end date = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)

select *
from table
where dateField between @start and @end

If your dateField is datetime then you need to add the seconds, or just add a day and use a < operand so you don't miss the last day

declare @start date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0)
declare @end date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)        --First day of current month

select *
from table
where dateField >=@start and dateField < @end
S3S
  • 3,578
  • 1
  • 14
  • 25