How to get the past 6 full months spanning a year

DECLARE @CurrentDate DATE, @StartDate DATE, @EndDate DATE
SET @CurrentDate=GetDate()
SET @EndDate = DateAdd(dd,-Day(@CurrentDate)+1,@CurrentDate)
SET @StartDate=DATEADD(mm,-6,@EndDate)
SELECT @CurrentDate AS CurrentDate, @StartDate AS StartDate,@EndDate AS EndDate

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

Just to mention :
I am using: -Day(@CurrentDate)+1 which will return the first day of the current month. Can be useful in case you want to get everything (including time ) from the previous month
this will return everything before December 1st:
<your column> <'12/1/2011'

while this will miss the records with date 11/30/2011 5:00 AM:
<your column> <='11/30/2011'

Howver if you want to get the last day of the previous month remove +1 and use just:
-Day(@CurrentDate)

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

Alternatively, if you use '0' as the starting date, you can work on a midnight to midnight basis:


soi là, soi carré

To put this in text book terms -

DATEDIFF (datepart, expression1, expression2)

or in code -

SELECT DATEDIFF(day, '2000-01-10','2000-01-05')

Result is -5 because 2000-01-05 is 5 days before 2000-01-10

I wound up creating a couple of functions to give me the dates I wanted, one for start date which I can then use to back out 6 months using the MONTH - 6, the other to give me end dates of the month which I can apply ot the query.

Many thanks to all for the suggestions and the explanations. They helped me figure it out.