Solved

Posted on 2005-04-12
270 Views
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
Question by:tatianak

LVL 68

Accepted Solution

@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

LVL 11

Expert Comment

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 Comment

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

LVL 68

Expert Comment

@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

Expert Comment

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

### Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.