Sql Agent Job skip last day of the month

Question

Post reply

Sql Agent Job skip last day of the month

Indianrock

SSC-Insane

Points: 20333
More actions
September 4, 2012 at 10:29 am

#267255

I don't see an elegant way to do this. Each job could have the main task only execute if something like the following shows today is not the last day of the month. Too bad the agent scheduler isn't more flexible. We're still on sql 2005.
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

You could just add some simple datepart logic.

declare @RunDate datetime

set @RunDate = '8/31/2012' --this would be getdate() in your code

select MONTH(@RunDate), MONTH(@RunDate + 1)

Then don't run your code when they are not equal. You could even add that to your where clause.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

I'd do what Sean suggested, with a simple IF .. THEN statement for execution of the item.

Indianrock SSC-Insane Points: 20333 More actions · Answer 3

Thanks, do you know if sql 2012 has a more flexible job scheduler? I can use something like this, but it means editing several jobs.

declare @RunDate datetime

set @RunDate = getdate()

--select Month(@RunDate), MONTH(@RunDate + 1)if Month(@RunDate)=MONTH(@RunDate + 1)

begin

print 'run job'

--exec DBA_Index_Update 0, 375000000,60

end

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 4

The scheduler is essentially the same in 2012.

I'm not sure what you want as "more flexible". An exception item to the schedule? Perhaps, but I think this is an extremely low value item to add to the platform. Changing a few jobs to add logic for this seems like a better solution than a much more complex scheduler.