find day - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

find day

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-04-23 : 08:06:23

Hi,
I would like to start a job at the end of the 10th business day of each month.
That day should not be a weekend.
How is this done please?
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-23 : 10:01:12

scheduled it every day and have your stored procedure check if it is not the 10th business day of the month don't do anything

or only execute if it is 10th business day of the month. Whichever way you like

KH
[spoiler]Time is always against us[/spoiler]

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-04-23 : 11:16:29

How do I execute it if it is 10th business day?
Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-23 : 11:19:37

Create a Calendar table. Add a column that is Business Day Of Month to that table. Run your job every day and check to see if the current day is equal to the Business Day of Month is equal to 10.

WAmin
Starting Member

16 Posts

Posted - 2014-04-24 : 01:10:04

Bit spoon feeding here but like what other mentioned

1- Create a job to run every day.
2- Create a store procedure which will run in that job.
Here is the code

DECLARE @TenthDayCOUNTER INT
SET @TenthDayCOUNTER=0
DECLARE @DayCounter INT
SET @DayCounter=0

WHILE @DayCounter<31
BEGIN
/**CHECK IF Days are between Monday to Friday**/
IF DATEPART(DW,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), @DayCounter)) BETWEEN 2 AND 6
SET @TenthDayCOUNTER=@TenthDayCOUNTER+1
/**CHECK IF Days are between Monday to Friday**/

/**CHECK IF YOUR 10th Busniess day hit**/
IF @TenthDayCOUNTER=10
BEGIN
/****RUN YOUR BUSINESS RULE HERE*****/
BREAK;
END
/**CHECK IF YOUR 10th Busniess day hit**/

SET @DayCounter=@DayCounter+1
END

Base on this code, 10th business day of current month is 04/14/2014.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources