Schedule Job

Question

Hi All, 

I was wondering how to schedule a job that run Last but 1 day of every month 

for ex:

in Sep the job needs to run on 29th (as 30th is last day in sep),

in Oct  the job needs to run on 30th (as 31st is last day in  Oct)

in Nov29 the job needs to run on 29th  ( as 30th is the last day of in Nov)

 likewise every month as a continuous process.

I know we can schedule job to run on 1, 2,3,4 and last day of every month.

Please advice.

Thanks,

Answer 1

Create a job and keep it not with schedule.

Now, execute your job in below query:

---------------

DECLARE @Today DATE = SYSDATETIME();

DECLARE @FirstDayNextMonth DATE = DATEADD(MONTH, 1,
DATEADD(DAY, 1-DAY(@Today), @Today));

DECLARE @LastWorkDayThisMonth DATE;

SELECT @LastWorkDayThisMonth = MAX(CalendarDate)
  FROM dbo.CalendarView -- guessing on name here
  WHERE WorkDay = 1
  AND CalendarDate >= @Today
  AND CalendarDate < @FirstDayNextMonth;

IF @LastWorkDayThisMonth = @Today
BEGIN
  -- do your last work day of the month stuff --HERE WITH

sp_start_job 'JOB name'
END

--------------

Similar discussion on below link:

http://dba.stackexchange.com/questions/97438/sql-job-to-be-run-earlier-on-last-work-day-of-month#comment176392_97438

---

Santosh Singh

Answer 2

Hello,

For every Job you can create 0-n schedules, so you can create a schedule for every month = 12 schedules.

Only February for leap years will be a bit complicate (or complex).

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 3

Excellent Santosh..Thanks

Answer 4

Satosh, do you have this CalendarView  handy?

Answer 5

never mind , i saw the link you provided now actually.

Thanks

Answer 6

Glad it helped you!

---

Santosh Singh