How to get the past 6 full months spanning a year

[bobpv]

Hello gurus!

I am confused by the DATEDIFF and DATEADD options when it comes to spanning a year, or when months do not all have 31 days..

I wish to grab all dates in a 6 month range of full months, which span a year. For example, if the query is run from March 15th, 2012, the desired result would be to get all dates for Feb,2012 (the prior full month- since March is not a full month, no results for that one), Jan, 2012, Dec, 2011,Nov, 2011 OCT, 2011 and Sept 2011.

Can anyone help me to understand the correct way of doing this?

 

[RTag]

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

http://www.R-Tag.com

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

 

[RTag]

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)

http://www.R-Tag.com

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

 

[drlex]

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

select  dateadd(mm,datediff(mm,0,getdate())-6,0),
dateadd(dd,datediff(dd,datepart(dd,getdate()),getdate()),0)

soi là, soi carré

 

[UnsolvedCoding]

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

 

[bobpv]

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.