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

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

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
1 Solution

Senior DBACommented:
@date DATETIME
)
RETURNS DATETIME
AS
BEGIN
--if current date is *not* a business day, keep adding one day until it is
BEGIN
SET @date = DATEADD(DAY, 1, @date)
END --WHILE
RETURN @date
END --FUNCTION
0

Commented:
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

Author 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

Senior DBACommented:
@date DATETIME
)
RETURNS DATETIME
AS
BEGIN
--if current date is *not* a business day, keep adding one day until it is
BEGIN
SET @date = DATEADD(DAY, 1, @date)
END --WHILE
SET @date = DATEADD(DAY, 1, @date)
--if 2nd day is *not* a business day, keep adding one day until it is
BEGIN
SET @date = DATEADD(DAY, 1, @date)
END --WHILE
RETURN @date
END --FUNCTION
0

Commented:
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

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