January 5, 2007 at 10:41 am
One minor problem in the recursive part of the function
The where clause 'holidayDate=@nextBusDay' will not evaluate
correctly if the @NextbusDay has a time part.
Need to change this to normalize the time part for both dates to 00:00:00 before comparing.
I loved the article though.Good work Rob.
April 26, 2007 at 10:43 am
This did not do it for me:
select dbo.GetNextBusinessDay(getdate(),1)
select dbo.GetNextBusinessDay(getdate()+1,1) -- Fri
select dbo.GetNextBusinessDay(getdate()+2,1) -- Sat
select dbo.GetNextBusinessDay(getdate()+3,1) -- Sun
------------------------------------------------------
2007-04-27 12:41:00
(1 row(s) affected)
------------------------------------------------------
2007-04-30 12:41:00
(1 row(s) affected)
------------------------------------------------------
2007-04-29 12:41:00
(1 row(s) affected)
------------------------------------------------------
2007-04-30 12:41:00
(1 row(s) affected)
September 19, 2007 at 10:07 am
I have the same problem as Thomas Nielson, it does not return the right value when the first parameter is a saturday try 3/31/2007, it returns 4/1 when it should return 4/2.
February 7, 2008 at 8:44 am
I was looking for a quick way to find the 5th business day of each month and this function handles it pretty well, except I have to find it from the end of last month, it seems, rather than the first of this month.
I used the following and it works pretty consistently so far, but I'm still testing.
declare @EndOfLastMonth datetime
select @EndOfLastMonth = dateadd(dd,-day(getdate()),convert(varchar(10),getdate(),120))
select dbo.fnGetNextBusinessDay (@EndOfLastMonth, 5)
Thanks Rob! This made a several-hours task into a several-minutes one.
February 7, 2008 at 10:25 am
While I fully agree with Joe that it becomes unwieldy without the dates table - there IS an algorithm that calculates Easter....Thought you might be interested.
http://users.sa.chariot.net.au/~gmarts/eastalg.htm
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 7, 2008 at 11:09 am
Matt Miller (2/7/2008)
While I fully agree with Joe that it becomes unwieldy without the dates table - there IS an algorithm that calculates Easter....Thought you might be interested.
Matt, While I also agree there is a SQL Holiday Algorithm referred to here. I think it is a corker...
http://www.sqlservercentral.com/Forums/Topic110259-23-1.aspx
Dave J
February 7, 2008 at 11:18 am
It certainly is - but in both cases - it just goes to show how unwieldy it would get to calculate every single holiday based on some crazy set of rules. If you're going to go through all of that trouble - calculate it and store it (never mind the bizarre add-on rules, like "observe the holiday on the monday if it falls on a sunday, except if it's a minor holiday which might be defined by theses x rules in the HR handbook...")
And Joe's point is a good one. there are multiple definitions of a lot of these....But even just using the definitions your org has picked out, seems silly to have to figure them out over and over again.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 20, 2008 at 7:42 am
without disagreeing with any of the above, I got bit by having a "list of holidays" table yesterday.
We have a table, unimaginatively called ms_holiday.
The structure is similar to
CREATE TABLE [dbo].[MS_Holiday] (
[Holiday_Depot_No] [varchar] (7) COLLATE Latin1_General_CI_AI NOT NULL ,
[Holiday_Dt] [datetime] NOT NULL ,
[Holiday_Desc] [varchar] (40) COLLATE Latin1_General_CI_AI NOT NULL
) ON [SECONDARY]
(I snipped out the created_by, created_date, constraints etc.).
This stores holidays for each depot in our organization, so we could store Western Easter dates at out London depots, and Orthodox Easter dates at our Greek(?) Depots . 😉
But it breaks down when we add new depots and forget to populate the table with the new depots and their corresponding dates! [Yes, it was me, guilty as charged... :blush:]
Therefore working things out on the fly for the times that these functions are called sounds a better bet for me.
Mind you, I could always go and beat up the developers of the app until they write a complete add new depot screen. (There are so many hacks I weep, regularly :()
Dave J
Edited: Took out a typo or two. How come you never spot them in the preview, only after post?
March 20, 2009 at 6:52 am
Just to add another version - I used Jason's code and added a parm for number of days to add, plus or minus:
[font="Courier New"]
CREATE FUNCTION dbo.GetNextBusinessDay (@Date datetime,
@DaysToAdd int)
RETURNS datetime
/* Name: GetNextBusinessDay
*
* Purpose: Calculate business days from a particular date. @DaysToAdd can be positive or negative.
*
* Modification History:
* 03/20/2009 VRI Derived from http://www.sqlservercentral.com/Forums/Topic228794-253-2.aspx "Jason"
*
*/
AS
BEGIN
DECLARE @DayCounter int,
@Sign int
SET @DayCounter = 0
SET @Sign = SIGN(@DaysToAdd)
WHILE (@DayCounter < ABS(@DaysToAdd))
BEGIN
SET @Date = DATEADD(dd, @Sign, CONVERT(datetime, (CONVERT(CHAR(10), @Date, 101))))
WHILE ((@@DATEFIRST + DATEPART(dw, @Date) - 1) % 7) + 1 IN (1, 7)
OR @Date IN (SELECT HolidayDate
FROM Holiday)
SET @Date = DATEADD(dd, @Sign, @Date)
SET @DayCounter = @DayCounter + 1
END
RETURN @Date
END[/font]
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply