October 23, 2007 at 7:40 am
Yep... mine definetly fails for different languages... that's why I said you need to modify it for other languages. 😀
Ken's DATEFIRST solution would fix that.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2007 at 7:52 am
It's actually Sergiy's (or rather I saw him post it the first time I saw it, long time ago) 😉
/Kenneth
October 23, 2007 at 8:42 am
The concept of calculating the number of work days between two dates and adding work days to a date are based on the concept of a start date and an end date. In all cases where I have seen this used it is always inclusive of the start date and end date. The best way to visualize this is to assume that the start date has a time stamp of 00:00:00 and the end date as a time stamp of 23:59:59.999... And it is sort of similar to the concept that when I'm told to start working I come in at 9:00 of the start date (and get payed for that day) and when I'm told to stop working I go home at 17:00 of the end date.
This lets me handle zero and negatives without having doubts (no swapping of dates please). Thus Workdays('2007-10-22','2007-20-23') should return 2 and Workdays('2007-10-23','2007-10-22') should return 0 and Workdays('2007-10-22','2007-10-19') should return 0 and Workdays('2007-10-24','2007-10-22') should return -1.
When a Saturday or Sunday is supplied for the start date then it should be considered the equivalent of the next Monday. And when a Saturday or Sunday is supplied for the end date then it should be considered the equivalent of the previous Friday. Both the Workdays and AddWorkdays function should accept a Saturday or Sunday and return results based on this definition.
The two functions should be mirrors of each other.
Thus for a given @ds and @i1
set @de=AddWorkdays(@ds,@i1)
set @i2=Workdays(@ds,@de)
@i2 should always result in a value equal to @1
Similarly for a given @ds and @de1
set @i=Workdays(@ds,@de1)
set @de2=AddWorkdays(@ds,@i)
@de2 should always result in a value equal to Normalize(@de1), where 'Normalize' applies the rule for an end date that falls on a Saturday or Sunday
Thus one can be used to test the other.
Jeff's Workdays works for all @ds less than or equale to@de but starts having problems when @ds is greater than @de where @ds and / or @de fall on a weekend.
As for AddWorkdays, so far I haven't found anything in these posts that works.
For example,
Matt: when I had 6 days to 2007-10-08 it returns 2007-10-16 whereas it should return 2007-10-15
Jeff: when I had 5 days to 2007-10-08 it returns 2007-10-15 whereas it should return 2007-10-12 (with third parameter set to 1)
October 23, 2007 at 8:50 am
Kenneth Wilhelmsson (10/23/2007)
If you want to know which day of the week a given date is, then there is exactly one way that will always give the correct answer, regardless of DATEFIRST settings, or language settings.
Funny - I thought you said there was exactly ONE way.....what about
datediff(dd,0,@dte)%7+1
Seems to work for me?
----------------------------------------------------------------------------------
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?
October 23, 2007 at 8:56 am
Michael Meierruth (10/23/2007)
And when a Saturday or Sunday is supplied for the end date then it should be considered the equivalent of the previous Friday. Both the Workdays and AddWorkdays function should accept a Saturday or Sunday and return results based on this definition.
Michael, I will never be supplying the end date, as it is the end date of which I am in need. I need the function to supply the end date, and I never want it to supply a Saturday or Sunday.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 23, 2007 at 10:08 am
On my server it means Monday, not Sunday.
That's fine, use the values that match your server or DateName() instead. Remember, these routines were developed to meet in-house needs. They were not developed with an eye for wide distribution. Some tweaking may be necessary.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 23, 2007 at 1:52 pm
Tomm Carr (10/23/2007)
On my server it means Monday, not Sunday.
That's fine, use the values that match your server or DateName() instead. Remember, these routines were developed to meet in-house needs. They were not developed with an eye for wide distribution. Some tweaking may be necessary.
That's not fine.
Settings on my DEV server don't match settings on AUS, UK or Hong-Kong Production servers where my databases suppose to be hosted.
And reliability of MY SYSTEM should not depend on freaky (or brilliant - it's the same) ideas of some local DBA.
_____________
Code for TallyGenerator
October 23, 2007 at 3:38 pm
Ok, fine. If you don't want to take the trouble to tweak it, I guess I will. It is my code, I suppose, so who better?
Here is a slight tweak of the affected code. Now it works no matter where the DBA has set the first day setting.
--If starting on a weekend, set to the next business day
if DatePart( dw, @StartDate ) + @@DateFirst = 8 -- Sunday
set @StartDate = @StartDate + 1;
else if DatePart( dw, @StartDate ) + (@@DateFirst % 7) = 7 -- Saturday
set @StartDate = @StartDate + 2;
--If ending on a weekend, set to the previous business day
if DatePart( dw, @EndDate ) + @@DateFirst = 8 -- Sunday
set @EndDate = @EndDate - 2;
else if DatePart( dw, @EndDate ) + (@@DateFirst % 7) = 7 -- Saturday
set @EndDate = @EndDate - 1;
Now you have your wish. The reliability of the system (well, this part at least) is independent of the machinations of the DBA--brilliant or otherwise.
Of course, now you have to consider Muslim countries. Don't they have a six day work week - with Sunday just another work day???
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 23, 2007 at 4:16 pm
Tomm Carr (10/23/2007)
Ok, fine. If you don't want to take the trouble to tweak it, I guess I will. It is my code, I suppose, so who better?
If we compare to mine - then mine is better.
I do it in one go, you do 2 tries.
And my option can be easily converted to set-based version. Can you do it with your code?
On previous page there is another approach from Matt Miller. Not sure it's faster than mine, but at least it's shorter to type. 🙂
Small improvement can make it even deterministic.
Way better that yours.
Of course, now you have to consider Muslim countries. Don't they have a six day work week - with Sunday just another work day???
Read the topic from the beginning.
I posted approach which would work for everyone.
This particular approach we discuss now was not about working days at all. It was about weekdays in European and American meaning of this word. No public holidays or other features encountered.
_____________
Code for TallyGenerator
October 23, 2007 at 8:05 pm
Sergiy (10/23/2007)
If we compare to mine - then mine is better.I do it in one go, you do 2 tries.
And my option can be easily converted to set-based version. Can you do it with your code?
Fine. But when you say "this won't work for me because of X" and I say "OK, I've fixed X" just to have you come back and say "it doesn't matter because it still has Y"... this is a little frustrating. Why even mention X when the problem all along was Y?
[Quote]On previous page there is another approach from Matt Miller. Not sure it's faster than mine, but at least it's shorter to type. 🙂
Small improvement can make it even deterministic.
Way better that yours.[/quote]
I have rewritten mine but before I get to that, I wanted to reiterate some rules that someone has already taken a stab at. These seem rather straightforward and it is what I designed toward.
* Adding one work day to a Saturday, Sunday or Monday should land you on the next Tuesday.
* Adding one work day to a Friday should land you on the following Monday.
* Adding one work day to any other day should land you on the next day.
* Subtracting one work day from a Friday, Saturday or Sunday should land you on the previous Thursday.
* Subtracting one work day from a Monday should land you on the previous Friday.
* Subtracting one work day from any other day should land you on the previous day.
* When adding work days (projecting into the future) and you land on a Saturday or Sunday, the result should "wrap" to the following Monday.
* When subtracting work days (going back in time) and you land on a Saturday or Sunday, the result should "wrap" to the previous Friday.
* When adding ZERO work days, the result should be the original date, even if it is a Saturday or Sunday. There is no way to determine if you should wrap forward to Monday or backward to Friday.
With those rules in mind, I selected the following dates and days for testing purposes:
select dbo.BizDaysAdd ('2007-10-20', 0) -- should be '2007-10-20' (unchanged)
select dbo.BizDaysAdd ('2007-10-20', 260) -- should be '2008-10-20' (52*5=260)
select dbo.BizDaysAdd ('2007-10-20', -260) -- should be '2006-10-20'
select dbo.BizDaysAdd ('2007-10-20', 1) -- should be '2007-10-23'
select dbo.BizDaysAdd ('2007-10-21', 1) -- should be '2007-10-23'
select dbo.BizDaysAdd ('2007-10-22', 1) -- should be '2007-10-23'
select dbo.BizDaysAdd ('2007-10-23', 1) -- should be '2007-10-24'
select dbo.BizDaysAdd ('2007-10-24', 1) -- should be '2007-10-25'
select dbo.BizDaysAdd ('2007-10-25', 1) -- should be '2007-10-26'
select dbo.BizDaysAdd ('2007-10-26', 1) -- should be '2007-10-29'
select dbo.BizDaysAdd ('2007-10-20', 2) -- should be '2007-10-24'
select dbo.BizDaysAdd ('2007-10-20', 3) -- should be '2007-10-25'
select dbo.BizDaysAdd ('2007-10-20', 4) -- should be '2007-10-26'
select dbo.BizDaysAdd ('2007-10-20', 5) -- should be '2007-10-29'
select dbo.BizDaysAdd ('2007-10-20', 6) -- should be '2007-10-30'
select dbo.BizDaysAdd ('2007-10-20', 7) -- should be '2007-10-31'
select dbo.BizDaysAdd ('2007-10-20', 8) -- should be '2007-11-01'
select dbo.BizDaysAdd ('2007-10-20', 9) -- should be '2007-11-02'
select dbo.BizDaysAdd ('2007-10-20', 10) -- should be '2007-11-05'
select dbo.BizDaysAdd ('2007-10-20', -1) -- should be '2007-10-18'
select dbo.BizDaysAdd ('2007-10-21', -1) -- should be '2007-10-18'
select dbo.BizDaysAdd ('2007-10-22', -1) -- should be '2007-10-19'
select dbo.BizDaysAdd ('2007-10-23', -1) -- should be '2007-10-22'
select dbo.BizDaysAdd ('2007-10-24', -1) -- should be '2007-10-23'
select dbo.BizDaysAdd ('2007-10-25', -1) -- should be '2007-10-24'
select dbo.BizDaysAdd ('2007-10-26', -1) -- should be '2007-10-25'
select dbo.BizDaysAdd ('2007-10-20', -2) -- should be '2007-10-17'
select dbo.BizDaysAdd ('2007-10-20', -3) -- should be '2007-10-16'
select dbo.BizDaysAdd ('2007-10-20', -4) -- should be '2007-10-15'
select dbo.BizDaysAdd ('2007-10-20', -5) -- should be '2007-10-12'
select dbo.BizDaysAdd ('2007-10-20', -6) -- should be '2007-10-11'
select dbo.BizDaysAdd ('2007-10-20', -7) -- should be '2007-10-10'
select dbo.BizDaysAdd ('2007-10-20', -8) -- should be '2007-10-09'
select dbo.BizDaysAdd ('2007-10-20', -9) -- should be '2007-10-08'
select dbo.BizDaysAdd ('2007-10-20', -10) -- should be '2007-10-05'
One quick test was to add one day to both Saturday and Sunday and make sure the result was the same, then subtract one day. However, in the drive to make a set-compatible algorithm, none of the functions you mention passed that test.
The rules I listed above are, of course, negotiable. But part of the reason this thread is now 9(!) pages long is the lack of a clear requirements document. 😀
I've decoupled my BizDaysAdd routine from the BizDaysBetween routine (it was originally meant as merely as a test, anyway). The result follows. I really don't see how it can be made set-based -- there are too many intermediate steps. The answer, performance-wise, is to write it in C# and make this a CLR function.
CREATE FUNCTION [dbo].[BizDaysAdd] (
@StartDate DateTime,
@Offset int
)
returns DateTime
as
begin
declare @Result Datetime,
@DayNum int;
select @Result = @StartDate,
@DayNum = DatePart( dw, @StartDate );
if @Offset > 0
begin
-- Set to next Monday if starting on a weekend.
select @Result = case
when @DayNum + @@DateFirst = 8 then
DateAdd( dd, 1, @StartDate )
when @DayNum + (@@DateFirst % 7) = 7 then
DateAdd( dd, 2, @StartDate )
else
@Result
end--case
-- Add five days for every week and then the remainder.
select @Result = DateAdd( d, @Offset % 5, DateAdd( wk, @Offset / 5, @Result ) );
select @DayNum = DatePart( dw, @Result );
select @Result = case
when @DayNum + @@DateFirst = 8 then
DateAdd( dd, 1, @Result )
when @DayNum + (@@DateFirst % 7) = 7 then
DateAdd( dd, 2, @Result )
else
@Result
end--case
end--end select 1 % 5
else if @Offset < 0
begin
-- Set to previous Friday if starting on a weekend.
select @Result = case
when @DayNum + @@DateFirst = 8 then
DateAdd( dd, -2, @StartDate )
when @DayNum + (@@DateFirst % 7) = 7 then
DateAdd( dd, -1, @StartDate )
else
@Result
end--case
-- Subtract five days for every week and then the remainder.
select @Result = DateAdd( d, @Offset % 5, DateAdd( wk, @Offset / 5, @Result ) );
select @DayNum = DatePart( dw, @Result );
select @Result = case
when @DayNum + @@DateFirst = 8 then
DateAdd( dd, -2, @Result )
when @DayNum + (@@DateFirst % 7) = 7 then
DateAdd( dd, -1, @Result )
else
@Result
end--case
end--else
return @Result;
end--Function
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 23, 2007 at 8:23 pm
[Code]select dbo.BizDaysAdd ('2007-10-20', 260) -- should be '2008-10-20' (52*5=260)
[/Code]
Actually 1 year normally contains 52 weeks AND 1 day (2008 contains 52 weeks and 2 days).
😀
should be '2008-10-18' (52*5=260)
_____________
Code for TallyGenerator
October 23, 2007 at 8:34 pm
Tomm Carr (10/23/2007)
I've decoupled my BizDaysAdd routine from the BizDaysBetween routine (it was originally meant as merely as a test, anyway). The result follows. I really don't see how it can be made set-based -- there are too many intermediate steps. The answer, performance-wise, is to write it in C# and make this a CLR function.
What was so wrong with my code?
Whole thing was done in 3 lines, and appears to do the job.
Do they pay you per line of code? 😉
I don't see any other reason for such long procedure with so many repeating checks.
:hehe:
_____________
Code for TallyGenerator
October 24, 2007 at 1:45 am
Sergiy (10/22/2007)
I said - I could not test.There was a typo in the formula.
Also I corrected small logical error in the code:
[Code]
IF OBJECT_ID('dbo.fnAddWeekdays_Sergiy','fn') IS NOT NULL
DROP FUNCTION dbo.fnAddWeekdays_Sergiy
GO
CREATE FUNCTION dbo.fnAddWeekdays_Sergiy (@Start DATETIME, @Days INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @End DATETIME
SELECT @End = @Start + @Days/5*7 + @Days%5 +
CASE WHEN (@@DATEFIRST + (DATEPART(dw, @Start) - 2)) % 7 + 1 + @Days%5 > 5
THEN 8 - ((@@DATEFIRST + (DATEPART(dw, @Start) - 2)) % 7 + 1 + @Days%5)
ELSE 0 END
RETURN @End
END
[/Code]
Seems all right now.
Nice job... even works when you drink warm beer 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2007 at 2:39 am
Matt Miller (10/23/2007)
Funny - I thought you said there was exactly ONE way.....what about
datediff(dd,0,@dte)%7+1
Seems to work for me?
Oh yes, using DATEDIFF that way will work, as long as:
the 2nd parameter's modulo is zero, or the 2nd parameter is an actual date that is a Monday.
(both are actually the same)
In datediff(dd, 0, @dte), the 0 is simply converted to '19000101', which happens to be a Monday...
So it coincides with the 'rule' that a Monday should be day #1. 😉
It works the same with any int that % 7 = 0
This is just a sideeffect of january first 1900 being a Monday as well.
The 'might break in the future' part here is, if the 'default date' in SQL Server should change from 19000101 to some other date, and that other date isn't a Monday.
(perhaps that is unlikely, but you never know...) 😉
With Sergiy's version, we don't need to worry about how 'default dates' are implemented or which weekday that date is.
/Kenneth
October 24, 2007 at 3:05 am
I tried
select dbo.fnAddWeekdays_Sergiy('20071017',4) and get '20071022'
select dbo.fnAddWeekdays_Sergiy('20071017',5) and get '20071024'
Can't remember if Sergiy's intention was to include the start date or not, but one of these two has got to be wrong.
Viewing 15 posts - 76 through 90 (of 117 total)
You must be logged in to reply to this topic. Login to reply