-1

I have a query that I thought I had cracked to return all records where the start date is between 18 and 9 months ago but I want all of the records for the month 18 months ago from the 1st of the month and all of the records for the month 9 months ago to the end of the month as well as the records in between. At the moment my WHERE clause contains:

WHERE startdate BETWEEN 
DATEADD(DAY, - (DAY(DATEADD(MONTH, 1, GETDATE())) - 18), DATEADD(MONTH, - 18, GETDATE())) 
AND DATEADD(DAY, - (DAY(DATEADD(MONTH, 1, GETDATE())) - 9), DATEADD(MONTH, - 9, GETDATE())

But it is pulling all records where start date is between 18 and 9 months ago to the exact date today and is not going back to the start of the month or completing the end month. Please could you advise where I have gone wrong. I am sure it is something really simple but I have gone bracket blind now I think.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
Caroline Allen
  • 183
  • 14

2 Answers2

1

You can use DateAdd and DateDiff to get the first day of the month, like shown in this answer.

From this it's fairly simple to do something like this:

WHERE StartDate >= DATEADD(month, -18, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
AND StartDate < DATEADD(month, -8, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
Community
  • 1
  • 1
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
-1

You can use new SQL EMONTH() end of month function

For the first of month, you can add 1 day to the previous month's end of month

Eralper
  • 6,461
  • 2
  • 21
  • 27
  • Downvote reason: `EOMONTH()` was introduced in sql server 2012. the OP is asking about Sql server 2008 r2. there is no `EOMONTH()` in this version. – Zohar Peled Sep 17 '15 at 08:33