Snippets of code to create date-range parameter defaults for SSRS reports.

PrecedingWeek

The seven days up to and including yesterday.

/*
Yesterday = Yesterday's date.
SevenDaysAgo = Seven days before today: i.e. a week up to and including yesterday.

12/08/2014 A Ing.

*/

DECLARE @Yesterday date = DATEADD(d, -1, CAST(getdate() AS date))
DECLARE @SevenDaysAgo date = DATEADD(d, -6, @Yesterday)
SELECT @Yesterday AS Yesterday,
@SevenDaysAgo AS SevenDaysAgo

Preceding Calendar Week (VB)

VBA to return start day of most recent complete week. Note the FirstDayOfWeek enumeration, which is different to the DayOfWeek enum shown in intellisense in the expression editor.

= DateAdd(DateInterval.Day,  0 - WeekDay( Today, FirstDayOfWeek.Tuesday), Today).ToString

Last Six Months – Full Weeks

That is, starts on Monday ends on a Sunday.

/*
	6 month period of full weeks, i.e. from Monday to Sunday.
	Uses two known dates - a Monday and a Sunday.

	To check:
		select datename(dw, @Monday), datename(dw, @Sunday)

	17/10/2014 A Ing.
*/

DECLARE @Monday DATE = '1900-01-01';
DECLARE @Sunday DATE = '1900-01-07';

SELECT 
	MondaySixMonthsAgo = DATEADD(WEEK, DATEDIFF(WEEK, @Monday, DATEADD(month, -6, getdate())), @Monday)
	,LastSunday = DATEADD(WEEK, DATEDIFF(WEEK, @Sunday, CURRENT_TIMESTAMP), @Sunday)

Month to Last Full Day

/*
	Month to up to last full day.
	@Yesterday : last full day
	@FirstDayOfYesterdaysMont : first day of month that Yesterday fell in.
 
	06/11/2014 A Ing.
 
*/

DECLARE @Yesterday date =  DATEADD(d, -1, CAST(getdate() AS date))
DECLARE @FirstDayOfYesterdaysMonth date = CAST(DATEADD(dd,-(DAY(@Yesterday) - 1), @Yesterday)  AS date) 
select
	 @FirstDayOfYesterdaysMonth AS FirstDayOfYesterdaysMonth 
	 ,@Yesterday AS Yesterday

 

Preceding Month – Preferred Version

/*
Start and end of previous month with respect to today's date.

12/08/2014 A Ing.
*/

DECLARE @LastDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(GETDATE())), GETDATE()) AS date) 
DECLARE @FirstDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(@LastDayOfPrecedingMonth)-1),@LastDayOfPrecedingMonth) AS date) 
SELECT
@FirstDayOfPrecedingMonth AS FirstDayOfPrecedingMonth
,@LastDayOfPrecedingMonth AS LastDayOfPrecedingMonth

Preceding Three Calendar Months

/*
Preceding Three Calendar Months
Previous three calendar months with respect to today's date.

29/01/2015 A Ing.
*/

DECLARE @LastDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(GETDATE())), GETDATE()) AS date) 
DECLARE @FirstDayOfThirdPrecedingMonth date = CAST(DATEADD(m, -2, DATEADD(dd,-(DAY(@LastDayOfPrecedingMonth)-1),@LastDayOfPrecedingMonth)) AS date) 
SELECT
@FirstDayOfThirdPrecedingMonth AS FirstDayOfThirdPrecedingMonth
,@LastDayOfPrecedingMonth AS LastDayOfPrecedingMonth

 Preceding Six Calendar Months

/*
Preceding Six Calendar Months
Previous six calendar months with respect to today's date.

20/04/2015 A Ing.
*/

DECLARE @LastDayOfPrecedingMonth date = CAST(DATEADD(dd,-(DAY(GETDATE())), GETDATE()) AS date) 
DECLARE @FirstDayOfSixthPrecedingMonth date = CAST(DATEADD(m, -5, DATEADD(dd,-(DAY(@LastDayOfPrecedingMonth)-1),@LastDayOfPrecedingMonth)) AS date) 
SELECT
@FirstDayOfSixthPrecedingMonth AS FirstDayOfSixthPrecedingMonth
,@LastDayOfPrecedingMonth AS LastDayOfPrecedingMonth

Preceding Month – Version 1

Ideal for reports scheduled for First of the Month, but won’t give current month-to-yesterday for any other day of the month.

/*
Yesterday = Yesterday’s date.
@FirstDayOfYesterdaysMonth = First day of the month that Yesterday fell in.

12/08/2014 A Ing.

*/

DECLARE @Yesterday date = DATEADD(d, -1, CAST(getdate() AS date))
DECLARE @FirstDayOfYesterdaysMonth date = CAST(DATEADD(dd,-(DAY(@Yesterday)-1),@Yesterday) AS date)
SELECT @Yesterday AS Yesterday,
@FirstDayOfYesterdaysMonth AS FirstDayOfYesterdaysMonth