May 24, 2018 at 8:20 am
hi,
I have a requirement to run the package only on 10th of every month , but 10th should not fall on weekends and holidays. If so , we need to run the following weekday .
Example if 10th is sunday , saturday or monday we need to run on tuesday.
or if 10th is holiday like july 4th,memoral day etc...then run on following working/business day.
So , here is my code but need to check holidays too . Any suggestions please?
Declare @Rundate date
SET @Rundate ='12/10/2017'
SELECT CASE WHEN (DATEPART(dd,@rundate)=10) AND (DATEPART(dw,@Rundate ) not in (7,2,1)) THEN 'Y'
WHEN (DATEPART(dd,@Rundate )=10) AND (DATEPART(dw,@Rundate) in (3)) THEN 'Y'
ELSE 'N' END
i added the above logic in my package to check not in sunday,saturday and monday ( Monday because our process do not run on monday's) then"Y" then it will run the next step, if not check for 10 if falls on tuesday then 'y' ( run the process).
Please suggest if there is better way to check weekends and holidays?
May 24, 2018 at 8:25 am
komal145 - Thursday, May 24, 2018 8:20 AMhi,
I have a requirement to run the package only on 10th of every month , but 10th should not fall on weekends and holidays. If so , we need to run the following weekday .
Example if 10th is sunday , saturday or monday we need to run on tuesday.
or if 10th is holiday like july 4th,memoral day etc...then run on following working/business day.So , here is my code but need to check holidays too . Any suggestions please?
Declare @Rundate date
SET @Rundate ='12/10/2017'SELECT CASE WHEN (DATEPART(dd,@rundate)=10) AND (DATEPART(dw,@Rundate ) not in (7,2,1)) THEN 'Y'
WHEN (DATEPART(dd,@Rundate )=10) AND (DATEPART(dw,@Rundate) in (3)) THEN 'Y'
ELSE 'N' ENDi added the above logic in my package to check not in sunday,saturday and monday ( Monday because our process do not run on monday's) then"Y" then it will run the next step, if not check for 10 if falls on tuesday then 'y' ( run the process).
Please suggest if there is better way to check weekends and holidays?
Haven't checked the code, but assuming it works, how are you going to configure the SQL Agent scheduler?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 24, 2018 at 9:03 am
Phil Parkin - Thursday, May 24, 2018 8:25 AMkomal145 - Thursday, May 24, 2018 8:20 AMhi,
I have a requirement to run the package only on 10th of every month , but 10th should not fall on weekends and holidays. If so , we need to run the following weekday .
Example if 10th is sunday , saturday or monday we need to run on tuesday.
or if 10th is holiday like july 4th,memoral day etc...then run on following working/business day.So , here is my code but need to check holidays too . Any suggestions please?
Declare @Rundate date
SET @Rundate ='12/10/2017'SELECT CASE WHEN (DATEPART(dd,@rundate)=10) AND (DATEPART(dw,@Rundate ) not in (7,2,1)) THEN 'Y'
WHEN (DATEPART(dd,@Rundate )=10) AND (DATEPART(dw,@Rundate) in (3)) THEN 'Y'
ELSE 'N' ENDi added the above logic in my package to check not in sunday,saturday and monday ( Monday because our process do not run on monday's) then"Y" then it will run the next step, if not check for 10 if falls on tuesday then 'y' ( run the process).
Please suggest if there is better way to check weekends and holidays?
Haven't checked the code, but assuming it works, how are you going to configure the SQL Agent scheduler?
Hmm ..my logic only checks the weekends , what is i need to check holidays? That was my question 🙂
May 24, 2018 at 10:10 am
You need a calendar table. Build one, stick it in a dba database, query it to run. You could even add flags for days and holidays. Makes life much simpler.
http://www.sqlservercentral.com/articles/T-SQL/70482/
May 24, 2018 at 10:31 am
komal145 - Thursday, May 24, 2018 9:03 AMPhil Parkin - Thursday, May 24, 2018 8:25 AMkomal145 - Thursday, May 24, 2018 8:20 AMhi,
I have a requirement to run the package only on 10th of every month , but 10th should not fall on weekends and holidays. If so , we need to run the following weekday .
Example if 10th is sunday , saturday or monday we need to run on tuesday.
or if 10th is holiday like july 4th,memoral day etc...then run on following working/business day.So , here is my code but need to check holidays too . Any suggestions please?
Declare @Rundate date
SET @Rundate ='12/10/2017'SELECT CASE WHEN (DATEPART(dd,@rundate)=10) AND (DATEPART(dw,@Rundate ) not in (7,2,1)) THEN 'Y'
WHEN (DATEPART(dd,@Rundate )=10) AND (DATEPART(dw,@Rundate) in (3)) THEN 'Y'
ELSE 'N' ENDi added the above logic in my package to check not in sunday,saturday and monday ( Monday because our process do not run on monday's) then"Y" then it will run the next step, if not check for 10 if falls on tuesday then 'y' ( run the process).
Please suggest if there is better way to check weekends and holidays?
Haven't checked the code, but assuming it works, how are you going to configure the SQL Agent scheduler?
Hmm ..my logic only checks the weekends , what is i need to check holidays? That was my question 🙂
Lacking a table that has all the holiday dates for your company in it, you'd be hard pressed to do that check. SQL Server does NOT have any internal holiday knowledge or tables, and even if it did, not all holidays are observed by all companies. If you don't have a table with your company's holiday dates, you'll have to create one, along with a business process to keep it up to date.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 24, 2018 at 10:36 am
sgmunson - Thursday, May 24, 2018 10:31 AMkomal145 - Thursday, May 24, 2018 9:03 AMPhil Parkin - Thursday, May 24, 2018 8:25 AMkomal145 - Thursday, May 24, 2018 8:20 AMhi,
I have a requirement to run the package only on 10th of every month , but 10th should not fall on weekends and holidays. If so , we need to run the following weekday .
Example if 10th is sunday , saturday or monday we need to run on tuesday.
or if 10th is holiday like july 4th,memoral day etc...then run on following working/business day.So , here is my code but need to check holidays too . Any suggestions please?
Declare @Rundate date
SET @Rundate ='12/10/2017'SELECT CASE WHEN (DATEPART(dd,@rundate)=10) AND (DATEPART(dw,@Rundate ) not in (7,2,1)) THEN 'Y'
WHEN (DATEPART(dd,@Rundate )=10) AND (DATEPART(dw,@Rundate) in (3)) THEN 'Y'
ELSE 'N' ENDi added the above logic in my package to check not in sunday,saturday and monday ( Monday because our process do not run on monday's) then"Y" then it will run the next step, if not check for 10 if falls on tuesday then 'y' ( run the process).
Please suggest if there is better way to check weekends and holidays?
Haven't checked the code, but assuming it works, how are you going to configure the SQL Agent scheduler?
Hmm ..my logic only checks the weekends , what is i need to check holidays? That was my question 🙂
Lacking a table that has all the holiday dates for your company in it, you'd be hard pressed to do that check. SQL Server does NOT have any internal holiday knowledge or tables, and even if it did, not all holidays are observed by all companies. If you don't have a table with your company's holiday dates, you'll have to create one, along with a business process to keep it up to date.
Ok Got it. Thank you.
May 24, 2018 at 10:36 am
komal145 - Thursday, May 24, 2018 9:03 AMPhil Parkin - Thursday, May 24, 2018 8:25 AMkomal145 - Thursday, May 24, 2018 8:20 AMhi,
I have a requirement to run the package only on 10th of every month , but 10th should not fall on weekends and holidays. If so , we need to run the following weekday .
Example if 10th is sunday , saturday or monday we need to run on tuesday.
or if 10th is holiday like july 4th,memoral day etc...then run on following working/business day.So , here is my code but need to check holidays too . Any suggestions please?
Declare @Rundate date
SET @Rundate ='12/10/2017'SELECT CASE WHEN (DATEPART(dd,@rundate)=10) AND (DATEPART(dw,@Rundate ) not in (7,2,1)) THEN 'Y'
WHEN (DATEPART(dd,@Rundate )=10) AND (DATEPART(dw,@Rundate) in (3)) THEN 'Y'
ELSE 'N' ENDi added the above logic in my package to check not in sunday,saturday and monday ( Monday because our process do not run on monday's) then"Y" then it will run the next step, if not check for 10 if falls on tuesday then 'y' ( run the process).
Please suggest if there is better way to check weekends and holidays?
Haven't checked the code, but assuming it works, how are you going to configure the SQL Agent scheduler?
Hmm ..my logic only checks the weekends , what is i need to check holidays? That was my question 🙂
I understood your question, but I was trying to make you think ahead to understand the possible pitfalls of what you are proposing, assuming you are going to run the package using SQL Agent.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply