SQLTeam.com | Weblogs | Forums

Weekly and Monthly Dates


#1

I have a Proc that runs reports(not SSRS), Weekly reports runs every Monday and Monthly on the first of the month. I have a @reportDate variable, which suppose to calculate the 7 previous days on Monday to run Weekly reports and previous month days on the 1 of every month to run Monthly reports.

I have this problem, For Weekly reports, should the report not run on Monday for whatever reasons, maybe the server was off, and it the process kicks off on a Tuesday or Thursday for that matter, the code still have to use the Monday date and run the weekly report, same goes for Monthly.
How do I solve this, thanks.


#2

How are you calculating the dates for the 7 days now?

The following will give you the date for the Monday immediately preceding today (unless today is Monday, in which case it will return today's date)

DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())/7*7,0)

Use that in place of whatever you were doing to pick up today's date and calculate the previous 7 days. For example, this will subtract 7 days from the Monday (thus giving you the Monday prior to that)

DATEADD(DAY, -7, DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())/7*7,0) )

Similarly, you can use the following to calculate the first of the current month.

DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)