question

Mohan avatar image
Mohan asked

get date

I need to write a function to return the required date. The function will allow two parameters one is date and noofdays, for the given date the no of days need to be added and the added date is saturday or sunday then we should get the coming monday. We have another table called calendar, suppose in that table monday is a holiday then we should get tuesday date. In case tuesday is also a holiday then we should get wednesday. Any help pls ...
t-sqlcalendar
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Interesting, how does your code looks like so far?
0 Likes 0 ·
If you've already got a calendar table, then this should be fairly easy. If you can post the DDL for your calendar table, we can work off that
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
See this example of one way to do it. I've created a small calendar table and added a field called `IsBusinessDay` - this is set to 0 for weekends and holidays. Then the query to add a number of days and find the next business day is trivial. declare @Calendar table ( calid int not null identity(1,1), [Date] date, [weekday] varchar(10), IsBusinessDay bit ) insert into @Calendar select top 60 cast(dateadd(day, N-1, '1 jan 2013') as date), datename(weekday, dateadd(day, N-1, '1 jan 2013')), case when datename(weekday, dateadd(day, N-1, '1 jan 2013')) in ('Saturday', 'Sunday') then 0 else 1 end from (select row_number()over(order by id) as N from syscolumns)a --set some days to be 'holidays' update @Calendar set IsBusinessDay = 0 where [Date] between '1 feb 2013' and '5 feb 2013' select * from @Calendar --- declare @date date declare @noofdays int --add 3 days to 8 Jan = 11 Jan set @date = '8 jan 2013' set @noofdays = 3 select top 1 [Date], * from @Calendar where Date >= dateadd(day, @noofdays, @date) and IsBusinessDay = 1 --add 5 days to 8 Jan = 13 Jan - but this is a Sunday, so return 14 Jan set @date = '8 jan 2013' set @noofdays = 5 select top 1 [Date], * from @Calendar where Date >= dateadd(day, @noofdays, @date) and IsBusinessDay = 1 --add 1 days to 31 Jan = 1 Feb - but this is a holiday - next available day is 6th Feb set @date = '31 jan 2013' set @noofdays =1 select top 1 [Date], * from @Calendar where Date >= dateadd(day, @noofdays, @date) and IsBusinessDay = 1
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nicely done, Kev. I have a suggestion and an observation, though. My suggestion is to use an explicit ORDER BY or someday the answer won't be what you expected. My observation is, if you span more than one weekend, your code appears to allow them to be counted as days. Considering the request for landing on a business day, I wonder if the OP understands that the code includes interim non-business days in the calculation?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.