Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 30
  • Last Modified:

how can I have future business day for my effective date

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
BehrangDBA
Asked:
BehrangDBA
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
SharathData EngineerCommented:
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
 
BehrangDBAAuthor Commented:
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now