SQLTeam.com | Weblogs | Forums

Weekly and Monthly Dates


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.


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)


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)


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