Solved

how can I have future business day for my effective date

Posted on 2016-09-19
4
18 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
ID: 41805380
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
ID: 41805676
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
ID: 41806534
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Detach & Attach 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.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

864 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

23 Experts available now in Live!

Get 1:1 Help Now