Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Finding the Next Business Day Recursively Expand / Collapse
Author
Message
Posted Friday, November 4, 2005 7:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 7, 2014 10:41 AM
Points: 20, Visits: 8

Here's another function that takes a simpler approach. If you are maintaining a holiday table, it would be simple enough to check the return value against it as well.

 

John

 

CREATE       FUNCTION dbo.udf_GetNextBizDay (@dDateValue DATETIME)
RETURNS Datetime
AS 
---------------------------------------------------------------------------------
--
-- Modification History:
--
-- Date                     Version  Programmer Comments
-- ------------------       -------  ---------- ----------------------------
-- 2005-06-13 02:23:00  1.0  John McLaughlin 
--
--
BEGIN

 DECLARE @dReturnDate DATETIME
 

 IF ISDATE(@dDateValue)=0
  BEGIN
   RETURN NULL
  END
 
 SET @dReturnDate = DATEADD(d,1,@dDateValue)
 --Set to zero hour. Uncomment if needed.
 --SET @dReturnDate = CAST(CONVERT(CHAR(10),@dReturnDate,101) AS DATETIME)
 WHILE DATEPART(dw,@dReturnDate) in(1,7)
  BEGIN
   SET @dReturnDate = DATEADD(d,1,@dReturnDate)
  END
 


 RETURN @dReturnDate
END

Post #235113
Posted Friday, November 3, 2006 9:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 12, 2009 10:14 AM
Points: 18, Visits: 167

I like the trick for handling the DATEFIRST.  With it, you can write the function in 3 lines without recursion, which I think is a bit more elegant.

CREATE FUNCTION dbo.fnGetNextBusinessDay (@Date datetime) RETURNS datetime AS
BEGIN
     SET @Date = DATEADD(dd, 1, 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, 1, @Date)
     RETURN @Date
END

 

Post #320361
Posted Friday, November 3, 2006 9:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, August 26, 2015 12:33 PM
Points: 20,627, Visits: 9,655

Hey Joe, don't you feel like you are talking for nothing sometimes???

 

Guys, Joe literally wrote the book on SQL, he wrote the ISO standards for SQL.  I would strongly suggest you listen to him on this one... and basically any other post he makes .

 

Even if it seems like overkill this time, and the next time and the time after that.  Won't you wish you had taken the time to write one when you decid to implement it the nth time... and then decid to make that the business rule to implement overall once you see the benefits??

Post #320377
Posted Thursday, November 9, 2006 5:57 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, August 23, 2015 7:49 PM
Points: 871, Visits: 298

I don't understand the @numDays input parameter!

For example if do this,

DECLARE @Date SMALLDATETIME

SET @Date = dbo.fnGetNextBusinessDay ('2006-12-24', 10)

SELECT @Date

It's suppsoe to return the date 2006-12-27 as that is the next business day.

Can someone explain please?




Kindest Regards,



Post #321807
Posted Friday, November 10, 2006 1:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:04 PM
Points: 6,518, Visits: 9,028

Hi Trigger,

The @numDays parameter is the number of business days to move forward in time.

To get 2006-12-27, you should have used:

SET @Date = dbo.fnGetNextBusinessDay ('2006-12-24', 1)

to get the "first" next business day. Your use of:

SET @Date = dbo.fnGetNextBusinessDay ('2006-12-24', 10)

should get the 10th next business day.




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #321870
Posted Friday, January 5, 2007 10:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 2013 7:09 AM
Points: 4, Visits: 17
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.
Post #334746
Posted Thursday, April 26, 2007 10:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 28, 2015 8:58 AM
Points: 6, Visits: 37

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)

Post #361241
Posted Wednesday, September 19, 2007 10:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 7, 2010 1:55 PM
Points: 1, Visits: 29
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.
Post #400424
Posted Thursday, February 7, 2008 8:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 8:00 AM
Points: 6, Visits: 25
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.



Post #452787
Posted Thursday, February 7, 2008 10:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:42 AM
Points: 7,372, Visits: 16,836
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?
Post #452844
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse