Solved

how can I have future business day for my effective date

Posted on 2016-09-19
16 Views
Hi every body,
I want to know how can I have next day as my current day except  weekend in T-SQL stored procedure? I have this update and I want to make change on it to fix this problem.
select 1, RIGHT(CONVERT(VARCHAR(10),GETDATE(),113),8) , RIGHT(CONVERT(VARCHAR(10),GETDATE()+1,113),8),
0
Question by:BehrangDBA

LVL 65

Accepted Solution

Jim Horn earned 250 total points
This question has been asked a lot, and I wrote the article SQL Server Calendar Table with downloadable code to handle business day logic, which includes weekends and holidays.

Good luck.
1

LVL 40

Assisted Solution

Sharath earned 250 total points
You can try like this to find the next working day. However this does not consider holidays.
``````DECLARE @workDays int, @calDays int
SET @workDays = 1 -- change this to get next Nth work day
-- using integer division to convert work weeks to calendar weeks
-- AND modulus division to get partial week's days
SELECT @calDays = @workDays / 5 * 7 + @workDays % 5
-- just double check that end result is not on a weekend
WHILE DatePart(dw, DateAdd(dd, @calDays, getdate())) IN (7, 1)
SET @calDays = @calDays + 1
-- select away you have your calendar days and date if you would like
-- 2016-09-20
``````
1

Assisted Solution

BehrangDBA earned 0 total points
I added  this part to my stored procedure and it's work:

declare @EffectiveDate datetime=GETDATE()

set @EffectiveDate =
(
select CASE DATENAME(DW,GETDATE())
WHEN  'SUNDAY'             THEN GETDATE() + 1
WHEN  'SATURDAY'       THEN GETDATE() + 2
WHEN  'FRIDAY'             THEN GETDATE() + 3
else GETDATE() + 1
END

)
0

Featured Post

Suggested Solutions

When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.