Job Schedule

Question

Job Schedule

runelords

Ten Centuries

Points: 1275
More actions
July 5, 2006 at 3:56 pm

#113527

Is there a way to schedule a job through SQL Agent to run on the last work day of a month? Any ideas how to implement this?

Arthur Lorenzini

Viewing 10 posts - 1 through 9 (of 9 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Yes, right-click on the job and select Properties. Go to the Schedules tab and click on Edit. Press the change button. Select the Monthly radio button. Under the Monthly Section, select 'The'. Look in the first drop down list for 'Last' and for 'Day' in the second drop down. This will let you run the job on the 'Last Day' of every x number of months.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Dick Schroth Right there with Babe Points: 725 More actions · Answer 2

Seems like you were asking for the last working day, not the last day. How about scheduling the job to run every day, and then run a proc that has an IF statement that calls a UDF which checks if it is the last working day. If you need to check holidays and such, you would probably need to build a table containing holidays, or if you just want to omit weekend days the function could do that without consulting a table.

Dick

runelords Ten Centuries Points: 1275 More actions · Answer 3

Last Working day is correct. I am not certain what the UDF would look like. Could you give me an example?

Arthur Lorenzini

Chrysa SSC Enthusiast Points: 123 More actions · Answer 4

Hi Runelords:

going back to the first reply, after choosing month, choose 'The'

next box choose 'last'

next box, choose 'WeekDay' this filters what you want i think

runelords Ten Centuries Points: 1275 More actions · Answer 5

It's close but I have to account for Holidays.

Arthur Lorenzini

Chrysa SSC Enthusiast Points: 123 More actions · Answer 6

I can't verify for sure - but i am presuming that the last weekday of any months i'm aware of don't have any statutory holidays --

i'll check tho

Chrysa SSC Enthusiast Points: 123 More actions · Answer 7

Nope - no Stat Holidays in the U.S. or Canada for ends of any months

http://www.timeanddate.com/calendar/index.html?year=2007&country=1

Terri-92562 SSCommitted Points: 1594 More actions · Answer 8

Nope - no Stat Holidays in the U.S. or Canada for ends of any months

Holidays can vary by city and state and year:

Memorial Day (US) can fall on May 31 on some years.

In my enterprise, December 31 is a holiday. Also, sometimes April 30 (Good Friday) is a holiday. February 28 is a holiday if it falls on Mardi Gras.

We have rely on a holiday table and a workday table to find the last working day of a month.

Terri

To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]

Chrysa SSC Enthusiast Points: 123 More actions · Answer 9

An excellent plan Terri - I apologize for misleading you Arthur