[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Calculate next business day

I already have a user defined function that checks whether an input date is a business day or not (checks for Sat/Sun and holidays in the holiday table). Now I need to create a new function that will return the next business day if the input date is not a business day.  For example, I input 4/2/05 which is a Saturday.  I would like the function to return 4/4/05 (Monday).  If I input 4/4/05, then the function would return the input date 4/4/05.  Thanks.    
0
tatianak
Asked:
tatianak
1 Solution
 
Scott PletcherSenior DBACommented:
CREATE FUNCTION dbo.NextBusinessDay (
    @date DATETIME
)
RETURNS DATETIME
AS
BEGIN
--if current date is *not* a business day, keep adding one day until it is
WHILE dbo.CheckBusinessDay (@date) = 0  --your current/existing function
BEGIN
    SET @date = DATEADD(DAY, 1, @date)
END --WHILE
RETURN @date
END --FUNCTION
0
 
Jokra_the_BarbarianCommented:
I would just throw a few conditional statements in, checking to see if the input date+n is found in the holiday table.

IF NOT EXISTS (SELECT * FROM holiday WHERE dtField = @inputDt)
  ' return the date @inputDt
IF NOT EXISTS (SELECT * FROM holiday WHERE dtField = DATEADD(day, 1, @inputDt))
  ' return the date DATEADD(day, 1, @inputDt)
IF NOT EXISTS (SELECT * FROM holiday WHERE dtField = DATEADD(day, 2, @inputDt))
  ' return the date DATEADD(day, 2, @inputDt)
IF NOT EXISTS (SELECT * FROM holiday WHERE dtField = DATEADD(day, 3, @inputDt))
  ' return the date DATEADD(day, 3 @inputDt)
IF NOT EXISTS (SELECT * FROM holiday WHERE dtField = DATEADD(day, 4, @inputDt))
  ' return the date DATEADD(day, 4, @inputDt)

You will need to add as many conditional statements as you have continuous period of non workdays. Most likely, the max number of non workdays will probably be 4.
0
 
tatianakAuthor Commented:
I tried the function from ScottPletcher and it works great. I was able to expand it to find the Previous business day.  Now, I'm trying to modify it to find 2nd business day.  Any ideas?
0
 
Scott PletcherSenior DBACommented:
CREATE FUNCTION dbo.SecondBusinessDay (
    @date DATETIME
)
RETURNS DATETIME
AS
BEGIN
--if current date is *not* a business day, keep adding one day until it is
WHILE dbo.CheckBusinessDay (@date) = 0  --your current/existing function
BEGIN
    SET @date = DATEADD(DAY, 1, @date)
END --WHILE
--add one more day, to get to 2nd business day
SET @date = DATEADD(DAY, 1, @date)
--if 2nd day is *not* a business day, keep adding one day until it is
WHILE dbo.CheckBusinessDay (@date) = 0  --your current/existing function
BEGIN
    SET @date = DATEADD(DAY, 1, @date)
END --WHILE
RETURN @date
END --FUNCTION
0
 
dodyryda1Commented:
hi, Im trying to adapt this for access need to enter the next business day in a field when a new record is created also referencing a list of holday dates in another table, how can I adapt the sql fuunction above for the visual basic event procedure editor? .. many thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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