Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how can I have future business day for my effective date

Posted on 2016-09-19
4
Medium Priority
?
27 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1000 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 41

Assisted Solution

by:Sharath
Sharath earned 1000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

609 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