Solved

how can I have future business day for my effective date

Posted on 2016-09-19
4
16 Views
Last Modified: 2016-11-04
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
Comment
Question by:BehrangDBA
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
Comment Utility
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

by:Sharath
Sharath earned 250 total points
Comment Utility
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
SELECT convert(date,DateAdd(dd, @calDays, getdate()))
-- 2016-09-20

Open in new window

1
 

Assisted Solution

by:BehrangDBA
BehrangDBA earned 0 total points
Comment Utility
Thanks guys for your answer and cunsidration,
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

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.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now