SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Add business day to dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

collie
Constraint Violating Yak Guru

399 Posts

Posted - 12/12/2013 :  10:58:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5944 Posts

Posted - 12/12/2013 :  11:12:21  Show Profile  Reply with Quote
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
Go to Top of Page

collie
Constraint Violating Yak Guru

399 Posts

Posted - 12/12/2013 :  15:19:04  Show Profile  Reply with Quote
I have a holiday dates table but it doesn't include Fridays and Saturdays.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17438 Posts

Posted - 12/12/2013 :  20:22:19  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17438 Posts

Posted - 12/12/2013 :  20:26:04  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

collie
Constraint Violating Yak Guru

399 Posts

Posted - 12/14/2013 :  13:27:26  Show Profile  Reply with Quote
Thanks for the responses. I will look into it when i go back to work.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000