tatianak
asked on
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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
@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
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
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.