locked
Schedule Job RRS feed

  • 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,

    Thursday, September 22, 2016 7:27 PM

Answers

  • 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

    • Marked as answer by Nick1990_K Friday, September 23, 2016 1:32 PM
    Thursday, September 22, 2016 8:40 PM

All replies

  • 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

    • Marked as answer by Nick1990_K Friday, September 23, 2016 1:32 PM
    Thursday, September 22, 2016 8:40 PM
  • 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]

    Friday, September 23, 2016 7:30 AM
  • Excellent Santosh..Thanks
    Friday, September 23, 2016 1:31 PM
  • Satosh, do you have this CalendarView  handy?
    Friday, September 23, 2016 1:45 PM
  • never mind , i saw the link you provided now actually.

    Thanks

    Friday, September 23, 2016 1:47 PM
  • Glad it helped you!

    Santosh Singh

    Friday, September 23, 2016 3:10 PM