I have two stored procedures
This code will first calculate the week in the current month.
By substracting the week number of the last day in the previous month from the week number of the current date.
That number is used to control which procedures are executed.
CREATE PROCEDURE dbo.spWeeklySometimesMonthly AS BEGIN SET NOCOUNT ON; DECLARE @week_in_month INT; SET @week_in_month = DATEPART(WEEK,GetDate())-DATEPART(WEEK,DATEADD(DAY,-DATEPART(DAY,GetDate()),GetDate())); IF (@week_in_month in (1,2,3,4)) BEGIN exec spWeek; END; IF (@week_in_month = 4) BEGIN exec spMonth; END; END
Do note that in Sql Server a week starts on Sunday.
So Saturday 2016-09-03 will have @week_in_month calculated as 0.
While Sunday 2016-09-04 calculates @week_in_month as 1.