?
Solved

how can I have future business day for my effective date

Posted on 2016-09-19
4
Medium Priority
?
25 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

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