Add business day to dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Add business day to dates

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-12-12 : 10:58:43

Hi,

I have a table holiday that stores dates of non-working days. However, it doesn't store dates for Sunday and Saturday which are also non-working days. How can I get the next working day from a date.
If i receive for example 12/13/2013 which is a Friday and I add a day then it's Saturday which isn't relevant. I need to return Monday's date but also check that the date doesn't exist in Holidays table.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-12 : 11:12:21

The most straight forward and reliable way to achieve this is with a business day calendar table including holidays.

But as for functions you can look at these threads:
fn_next_business_day
Date Table Function F_TABLE_DATE

Be One with the Optimizer
TG

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-12-12 : 15:19:04

I have a holiday dates table but it doesn't include Fridays and Saturdays.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-12 : 20:22:19

What TG mean is to use a "Business Day" calendar. Business day calendar contains all the dates including holidays, weekends etc.
And in this table, you can have a flag to indicate that it is a working day or non-working day.

Once you have that, the query can be simply


select top 1 [date]
from   [Business Day Calendar]
where  working_day = 1
and    [date] > '2013-12-13'
order by [date]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-12 : 20:26:04

alternatively, you can also make use of F_TABLE_DATE if you can't create a "Business Day" calendar for whatever reason


SELECT TOP 1 c.[DATE]
FROM    F_TABLE_DATE('2013-12-13', '2013-12-31') c
WHERE   NOT EXISTS (SELECT * FROM holiday x WHERE x.[DATE] = c.[DATE])
AND     WEEKDAY_NAME NOT IN ('SAT', 'SUN')
ORDER BY c.[DATE]

KH
[spoiler]Time is always against us[/spoiler]

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-12-14 : 13:27:26

Thanks for the responses. I will look into it when i go back to work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources