• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

check current date

I only want to run some jobs on M - F, excluding the weekends.  i also need to exclude holidays.
i'm struggling right now to use my calendar table in the job scheduling, which simply is a calendar table, so to speak, indicative of whether the dates in the table are weekdays, weekends, holidays, etc.  Ultimately, I will use this, but right now I really need to modify the job scheduling post haste.  
So, in the interim, what's the right way to only invoke the proc if it's M, T, W, Th or F ?
i thought i could just preface it with an IF, making it execute conditionally, but, it's not working.  (syntax)
what's the right way to do this?

IF (SELECT datepart(dw, getdate()) BETWEEN 2 AND 6
do this.....
0
dbaSQL
Asked:
dbaSQL
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

DECLARE @dw INT
SET @dw = datepart(dw, getdate())  
IF (@dw >= 2 and @dw <= 6)
BEGIN
  -- do this
END
0
 
HDatabase AdministratorCommented:
I believe it would be much easier to schedule these procs in Sql server Agent's job scheduler then you can pick the specific dates?
0
 
Scott PletcherSenior DBACommented:
SET DATEFIRST 7  --just to be sure
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
BEGIN
    EXEC ...
    ...other command(s)...
END --IF
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dbaSQLAuthor Commented:
well, in retrospect, you are right hollecar -- if, in fact, i only want to run M - F, sql server agent scheduling is quite fine, as it allows me to specifically schedule just that - M thru F and/or Saturday and Sunday.  angel and scott, both your suggestions do just that, too.  so, for the way I phrased myself, you've each answered my question, and I will split the points to each of you.  forgive me for having been a bit weird w/this question.

my problem remains, though, as I still cannot exclude holidays.  basically, i've got a whole bunch of agent jobs which i have to vpn in and disable intermittently on holidays and non-work days such that i'm not doing things like sending out blank reports of 'today's trades' for Christmas.  (you know what i mean)  

as i eluded above, I have a calendar table which has 2 years of dates -- attributes for each record are Year, MonthName, DayName, isHoliday, isWeekDay,  etc.  So all the info is there - i just need to someow utilize this with my job scheduling.  any insight or suggestions at all?  i thought possibly a udf that could be called within my procedures, but as of yet, i'm just not getting it together.  my table definition is below, would you guys have any direction/suggestions for me on this?

date - date in smalldatetime
Weekday  - bit  (is it a weekday?  )
Holiday   - bit  (is it a holiday? i.e., christmas, easter, etc., populated manually)
YR - Year
FY - fiscal year
Q - quarter
M - numerical representaiton of month
D - numerical representaiton of day
DW -numerical representaiton of week day
monthname - January, February, etc.
dayname - Monday, Tuesday, etc.
W - week number in the year that date falls in
HolidayDesc -- Christmas, Thanks Giving, etc.
0
 
Scott PletcherSenior DBACommented:
SET DATEFIRST 7  --just to be sure
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
--add this code
AND NOT EXISTS (SELECT 1 FROM calendar WHERE date = CONVERT(CHAR(8), GETDATE(), 112) AND holiday = 1)
--end: add
BEGIN
    EXEC ...
    ...other command(s)...
END --IF
0
 
dbaSQLAuthor Commented:
oh, oh, very good, scott.  i could do this across the board, yes?  like i've got N number of different agent jobs which simply invoke procedures and do this and that.  couldn't i just preface the procedures w/this?
0
 
Scott PletcherSenior DBACommented:
Yes, I would think so :-) .
0
 
dbaSQLAuthor Commented:
excellent, scott.  it works beautifully.  
i am splitting the points, as i said last night, and just forgot to do.
each of you, thanks very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now