Post reply

Scheduling a Processing and Snapshot for the Last Calendar Day of Each Month

  • June 28, 2012 at 10:40 am

    #262916

    Hi, is there a way to schedule my processing and snapshot for the last calendar day of each month? There are options for the last week of each month and for a specific day of each month, but since the last calendar day of each month changes month by month, I need an criterion specifically for that.

  • June 28, 2012 at 11:08 am

    #1506758

    here's one way to do it:

    create a scheduled job that runs every day, but only does real work when the dates match the last day of the month:

    here's the formula, featuring dateadd/datediff, to determine midnight of today, and midnight of the last day of the current month, calculated dynamically.

    /*--results

    2012-06-28 00:00:00.0002012-06-30 00:00:00.000

    */

    select

    --midnite of TODAY

    DATEADD(dd, DATEDIFF(dd,0,getdate()), 0),

    --midnight of the last day of the month

    dateadd(dd,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+ 1, 0))

    so your schedule job has code that is something like this:

    --if midnite today == midnight last day of month

    IF DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) = dateadd(dd,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+ 1, 0))

    BEGIN

    PRINT 'Doing stuff'

    --code to do stuff goes here

    END

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply