December 27, 2007 at 7:50 am
I got it, I missed out OUTPUT when declare @workdays as INT. Thanks
December 28, 2007 at 1:06 pm
There is a problem with the function and it is basically a problem with counting days. To illustrate, let's use something that is not a date. Say we have two bags of marbles, one with six marbles and one with ten marbles. We can ask, "What is the difference in the number of marbles between the two bags?" The answer is four. We can say that the smaller bag has four fewer marbles or that the larger bag has four more marbles but the difference between them is four. What about two bags with ten marbles each? The answer is zero. There is no difference. If we add a marble to one of the bags, the answer becomes one. And so forth.
Actually, this is a way to check that we have the right answer. We start out with two bags of six marbles each. What is the difference? Zero. We add one marble to a bag and add one to the answer. What is the difference now? One. We add another marble to the same bag and add one more to the answer. We continue until the bag has ten marbles. We see that the answer has been incremented to four so we have verified the answer to the first question above.
Now let's do the same thing with dates. Let's start out with the same date, say '12/24/2007' and '12/24/2007' (which happens to be last Monday). What is the difference (in days) between them? There is no difference between them, they are the same date. So the answer is zero. We can use DateDiff as a check: select DateDiff(d, '12/24/2007', '12/24/2007') returns a zero, just as we expect. If we add one day to one of the dates then we also add one to our answer. So the number of days difference between '12/24/2007' and '12/25/2007' we can expect to be one. A quick call to DateDiff verifies that we are correct.
Let's say we are measuring the number of work/week/business days between two events. The first event is when a certain process starts and the second event is when the process completes. However, we know that the process always starts on a Monday and always completes on or before the following Friday. With these constraints, we don't need to worry about crossing over weekends so we don't need a specific "workday only" calculator -- DateDiff should work just fine for this application. And indeed it does. Given a start date of '12/24/2007' and a complete date of '12/27/2007', we get back an answer of three.
However, the function fn_WorkDays returns an answer of four for those dates. As a matter of fact, when we test fn_WorkDays against DateDiff, for dates that do not span a weekend, there is always one too many days. The same date, where we expect no difference, returns one. That means there is one day somewhere between '12/24/2007' and '12/24/2007' and two days between '12/24/2007' and '12/25/2007'.
Why do I go to such length to make this point? Because I have had this discussion before. Apparently there is only one day between Monday and Tuesday when considering them as ordinary days but there are two days between Monday and Tuesday when considering them as Work Days. Why this is so has never been adequately explained to me but I have been told that this is how most companies count work days. None that I have worked for, mind you, but with less than a dozen employers under my belt, that is too few to detect an industry-wide trend. However, I sincerely hope this is not true -- that companies do not tack on an extra day when working with work days rather than ordinary calendar days. That would make working with work days (nice poetic phrase if you say it out loud: "working with work days") a good order of magnitude more difficult than it already is.
So what is the answer? How many days are between two adjacent work days: one or two? How many days are between a work day and itself: zero or one? We can't write or use a "work day calculator" until we answer those questions.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
July 10, 2008 at 12:08 pm
Overall a really good function but I hate error codes coming back from a function and our environment wanted us to return negative numbers when the start date was greater than the end date....so I added a few checks for the datetime parms and a third parameter...
USE [MASTER]
GO
/****** Object: UserDefinedFunction [dbo].[FUNC_WorkDays] Script Date: 07/10/2008 13:38:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter FUNCTION [dbo].[fn_WorkDays]
/***************************************************************************************
Purpose:
1. Given any valid start date and end date, this function will calculate and return
the number of workdays (Mon - Fri).
2. Given only a valid start date (end date has DEFAULT in it), this function will
return a 1 if the start date is a weekday and a 0 if not a weekday.
Usage:
1. dbo.fn_WorkDays(@StartDate,@EndDate,'')
2. dbo.fn_WorkDays(@StartDate,DEFAULT,'') --Always returns 1 or 0
3. dbo.fn_WorkDays(@EndDate,@StartDate,'')
4. dbo.fn_WorkDays(@StartDate,@StartDate,'') --Always returns 1 or 0
5. dbo.fn_WorkDays(@EndDate,@EndDate,'') --Always returns 1 or 0
6. dbo.fn_WorkDays(@StartDate,@EndDate,'Y') - if startdate < enddate result will be negative
Notes:
1. Holidays are NOT considered.
2. Because of the way SQL Server calculates weeks and named days of the week, no
special consideration for the value of DATEFIRST is given. In other words, it
doesn't matter what DATEFIRST is set to for this function.
3. If the input dates are in the incorrect order, they will be reversed prior to any
calculations.
4. Only whole days are considered. Times are NOT used.
5. The number of workdays INCLUDES both dates
6. Inputs may be literal representations of dates, datetime datatypes, numbers that
represent the number of days since 1/1/1900 00:00:00.000, or anything else that can
be implicitly converted to or already is a datetime datatype.
7. Undocumented: The DATEPART(dw,date) does not actually count weeks... It counts the
transition to a Sunday regardless of the DATEFIRST setting. In essence, it counts
only whole weekends in any given date range.
8. This UDF does NOT create a tally table or sequence table to operate. Not only is
it set based, it is truly "tableless".
Error Indications:
1. If either the @StartDate or the @EndDate parameter is an invalid date, the
following error is returned...
"Server: Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value."
2. If either the @StartDate or the @EndDate parameter is a string not resembling a
date, the following error is returned...
"Server: Msg 241, Level 16, State 1, Line 3
Syntax error converting datetime from character string."
3. If only one parameter is passed, the following error is returned...
"Server: Msg 313, Level 16, State 2, Line 3
An insufficient number of arguments were supplied for the procedure or
function MASTER.dbo.fn_WorkDays."
Revisions:
Rev 00 - 12/12/2004 - Jeff Moden - Initial creation and test.
Rev 01 - 12/12/2004 - Jeff Moden - Load test, cleanup, document, release.
Rev 02 - 12/26/2004 - Jeff Moden - Return NULL if @StartDate is NULL or DEFAULT and
modify to be insensitive to DATEFIRST settings.
***************************************************************************************/
--======================================================================================
-- Presets
--======================================================================================
--===== Define the input parameters (ok if reversed by mistake)
(
@StartDateX VARCHAR(22),
@EndDateX VARCHAR(22) = NULL, --@EndDate replaced by @StartDate when DEFAULTed
@Swapit char(1) = NULL -- if not null then don't swap
)
--===== Define the output data type
RETURNS INT
AS
--======================================================================================
-- Calculate the RETURN of the function
--======================================================================================
BEGIN
--===== Declare local variables
--Temporarily holds @EndDate during date reversal
DECLARE @Swap DATETIME
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @Reverser int
--===== RAR If StartDate or EndDate is not valid date, return NULL
IF ISDATE(@StartDateX) = 0
Select @StartDate = NULL
IF ISDATE(@StartDateX) = 1
Select @StartDate = @StartDateX
IF ISDATE(@EndDateX) = 0
Select @EndDate = NULL
IF ISDATE(@EndDateX) = 1
Select @EndDate = @EndDateX
--===== If the Start Date is null, return a NULL and exit
IF @StartDate IS NULL
RETURN NULL
--===== If the End Date is null, populate with Start Date value
-- so will have two dates (required by DATEDIFF below)
IF @EndDate IS NULL
SELECT @EndDate = @StartDate
--===== Strip the time element from both dates (just to be safe) by converting
-- to whole days and back to a date. Usually faster than CONVERT.
-- 0 is a date (01/01/1900 00:00:00.000)
SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),
@EndDate = DATEADD(dd,DATEDIFF(dd,0,@EndDate) ,0)
--===== RAR set the reverser to negative or positive
SELECT @Reverser = 1
IF @Swapit = 'Y' and @StartDate > @EndDate
Select @Reverser = -1
--===== If the inputs are in the wrong order, reverse them
IF @StartDate > @EndDate
SELECT @Swap = @EndDate,
@EndDate = @StartDate,
@StartDate = @Swap
--===== Calculate and return the number of workdays using the
-- input parameters. This is the meat of the function.
-- This is really just one formula with a couple of parts
-- that are listed on separate lines for documentation
-- purposes.
RETURN (
SELECT
--Start with total number of days including weekends
(DATEDIFF(dd,@StartDate,@EndDate)+1)
--Subtact 2 days for each full weekend
-(DATEDIFF(wk,@StartDate,@EndDate)*2)
--If StartDate is a Sunday, Subtract 1
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday'
THEN 1
ELSE 0
END)
--If EndDate is a Saturday, Subtract 1
-(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday'
THEN 1
ELSE 0
END)
) * @Reverser
END
Rick
do it right, or do it over and over, it's up to you
September 8, 2008 at 10:11 am
ok i know this is a serious newb question and someone else had an issue when they tried to run this as an sp. i'm trying to run this as a function...created the function by copy pasting entire script posted on article..
all i get when i call the function is command completed successfully. just doesnt return a value. am i missing something here?
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '07/14/2008' --TESTING
SET @enddate = '07/7/2008'
exec fn_WorkDays @startdate,@enddate
help!
September 8, 2008 at 11:59 am
dulanjan (9/8/2008)
exec fn_WorkDays @startdate,@enddatehelp!
Use exec to call a stored procedure. When calling a function, you have to capture the returned value for further processing and/or display.
declare @WorkDayCount int;
set @WorkDayCount = dbo.fn_WorkDays( @Startdate, @Enddate );
select @WorkDayCount as ResultOfFunctionCall;
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
September 8, 2008 at 12:09 pm
ohhh i see. wow. thanks a lot. never dawned on me.
thank you very much.
September 8, 2008 at 12:27 pm
is there anyway to edit this function so that it will return a value without declaring an output parameter?
like how datediff or other functions work? simply two parameters and enclosed in parameters and it gets that value?
September 8, 2008 at 12:54 pm
Use it any way you would use any function:
if DateDiff( dd, @Startdate, @EndDate ) > dbo.fn_Workdays( @Startdate, @EndDate ) begin
-- There is at least one weekend between the dates
exec DoSomethingAboutIt...
end;
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
September 8, 2008 at 1:33 pm
excellent! i was trying it without the dbo part, trying to use it as a system function i guess 🙂
you're the man!
September 12, 2008 at 8:55 am
jeff...i came up with an interesting dilemma with your function.
if the start date is a saturday and the end date is a sunday it returns a -1.
i think this should just return 0.
no?
September 13, 2008 at 10:34 am
dulanjan (9/12/2008)
jeff...i came up with an interesting dilemma with your function.if the start date is a saturday and the end date is a sunday it returns a -1.
i think this should just return 0.
no?
Heh... would you mind posting the exact code you tested with, please?
--Jeff Moden
September 15, 2008 at 7:30 am
i just cut and paste from your code. (dont worry i left your name on there :D)
so yeah if you put in any consecutive saturday and sunday you get a -1. try the 13th and 14 of this month!
September 15, 2008 at 10:28 am
That's what I really meant... what were the inputs. Thanks for the 13th/14th of this month. I'll give it a try.
--Jeff Moden
September 15, 2008 at 5:57 pm
dulanjan (9/15/2008)
i just cut and paste from your code. (dont worry i left your name on there :D)so yeah if you put in any consecutive saturday and sunday you get a -1. try the 13th and 14 of this month!
Not sure what you're doing differently, but I get 0 from both the "raw" code and the function...
Here's what I ran...
DECLARE @StartDate DATETIME,
@EndDate DATETIME
SELECT @StartDate = '20080913',
@EndDate = '20080914'
SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) AS RawCode
SELECT dbo.fn_WorkDays(@StartDate,@EndDate) AS FunctionCode
... and here are the results...
RawCode
-----------
0
(1 row(s) affected)
FunctionCode
------------
0
(1 row(s) affected)
--Jeff Moden
October 22, 2008 at 9:22 am
Jeff/Forum,
I love the simplicity of the function, and the fact that it does return the number of business days between two dates. However, what if I needed to create a trigger to update a FOLLOWUP (DATETIME column) automatically with a date that is either 3 or 15 business days out (not worried about holidays). So I try the following:
DECLARE @BizDays15 TINYINT
,@StartDate DATETIME
,@EndDate DATETIME
SET @StartDate = '10/1/2008'
Set @EndDAte = @StartDate + 15
SET @BizDays15 = (SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END))
select @BizDays15, @StartDate, @endDAte + 3
select case
when @BizDays15 < 15
then (select @EndDate + (15-@BizDays15))
else @EndDate
end
My result when I do this is the 19th of Oct. which is a Sunday L. Of course I need it to be the 22nd of Oct. Any thoughts on this?
Thanks,
Fraggle
Viewing 15 posts - 91 through 105 (of 156 total)
You must be logged in to reply to this topic. Login to reply