October 25, 2007 at 7:10 pm
If anyone is interested, here is a function that will return a DateFirst-independent value for the day of the week.
/*
Author:
Tomm Carr
Create date:
10/25/2007
Description:
Returns the day of the week independent of the DateFirst setting.
1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday
*/
create function [dbo].[DayOfWeek] (
@TargetDate datetime
)
returns int
as
begin
return (DatePart( dw, @TargetDate ) + @@DateFirst - 1 ) % 7 + 1;
end--Function
As you can see, it turns out to be quite simple.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 25, 2007 at 7:18 pm
Are you sure Tom Carr is an author?
Would you mind to look couple of pages back?
It's just almost exact copy of my script. "+6" is replaced with "-1" - that's all.
And if you search this forum you may find same formula in my post from somewhere 3 years ago.
So, you're clearly not the author.
_____________
Code for TallyGenerator
October 25, 2007 at 8:34 pm
Here is my (hopefully) final submission. The trouble with all the previous submissions (including mine) was that they couldn't handle a few days that went over a weekend. Given a date that was on a Friday and adding 3 days resulted in the next Monday. Unfortunately, the correct answer is Wednesday. The starting date was a work day and the result was a work day so there was nothing to indicate a correction was needed.
This function seems to handle any and all of those problems whether adding or subtracting.
PS: I have included the code starting from the CREATE FUNCTION line -- omitting the preceding comments which include my name (don't want to offend any delicate sensibilities).
So to give a brief synopsis: If the starting date is a Saturday or Sunday, it is changed to the following Monday. What happens next depends on if the days being added is positive or negative. We have to do this. If we add one day to a Friday we land on Saturday; if we subtract two days from Monday we also land on Saturday. We have to make the final adjustment according to the direction we were going (back to the future or into the past).
When going forward: the date has already been adjusted so it is Monday through Friday. We subtract the number of days necessary to get it to Monday -- adding that amount to the original number of days we're supposed to be adding. Then adjust that amount by adding two days per five-day week and add that many days to the date. Finally, one last check to see if we landed on a weekend day and add another two days if we did. (If the day is Saturday, we are one working day past Friday, which is Monday. If the day is Sunday, we are two working days past Friday, which is Tuesday. So if we land on Saturday or Sunday, add two extra days.)
When going backward: set the launch date to Friday but otherwise it is the same procedure, just making sure we are adjusting backwards instead of forwards. Such as: if our final date is a weekend day, adjust by subtracting two days.
create function [dbo].[BizDaysAdd] (
@StartDate DateTime,
@Offset int
)
returns DateTime
as
begin
declare @Result Datetime,
@DayNum int;
select @Result = @StartDate,
@DayNum = dbo.DayOfWeek( @StartDate );
-- If starting on a weekend, set to the next Monday
if @DayNum = 1 -- Sunday
select @Result = DateAdd( dd, 1, @StartDate );
else if @DayNum = 7 -- Saturday
select @Result = DateAdd( dd, 2, @StartDate );
if @Offset > 0
begin
-- Now set @DayNum to the offset needed to move the day to the previous Monday.
-- At this point, as we have already moved any Saturday or Sunday to Monday, the
-- daypart will be 2 (Monday) through 6 (Friday), so this will only effect Tuesday
-- through Friday. Use the FirstDay-independent function to get the day part.
select @DayNum = dbo.DayOfWeek( @Result ) - 2;
-- Now adjust. After this point, the date we have will be a Monday.
select @Result = DateAdd( d, -@DayNum, @Result );
-- To compensate, add that amount to the offset
select @Offset = @Offset + @DayNum;
-- Adjust Business days to actual calendar days
select @Offset = @Offset + ((@Offset / 5) * 2);
-- and add
select @Result = DateAdd( d, @Offset, @Result );
select @DayNum = dbo.DayOfWeek( @Result );
-- If we "landed" on a Saturday, it should have been the next Monday. If we
-- landed on a Sunday, it should have been the next Tuesday. Either way,
-- be ready to make one last adjustment.
if @DayNum in (1, 7)
select @Result = DateAdd( dd, 2, @Result );
end--if
else if @Offset < 0
begin
-- We're subtracting days so we do a few things just a little differently.
-- Set @DayNum to the offset needed to move the day to the next Friday.
-- At this point, all days are 2 (Monday) through 6 (Friday)
select @DayNum = 6 - dbo.DayOfWeek( @Result );
-- Now adjust. After this point, the date we have will be a Friday.
select @Result = DateAdd( d, @DayNum, @Result );
-- To compensate, subtract that amount from the offset
select @Offset = @Offset - @DayNum;
-- Adjust Business days to actual calendar days
select @Offset = @Offset + ((@Offset / 5) * 2);
-- and subtr... um, add
select @Result = DateAdd( d, @Offset, @Result );
-- If we "landed" on a Sunday, it should have been the previous Friday. If we
-- landed on a Saturday, it should have been the previous Thursday. Either way,
-- be ready to make one last adjustment.
select @DayNum = dbo.DayOfWeek( @Result );
if @DayNum in (1, 7)
select @Result = DateAdd( dd, -2, @Result );
end--else
return @Result;
end--Function
Again, I haven't tested this on a million row table, but the performance is going to be about as good as it can be to still return the correct answer. I still say that if the performance turns out to be a problem, you need to code this in C#.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 26, 2007 at 12:44 am
By the way, here is another simple way to eliminate the datepart headache:
-- capture these for known dates as they may vary based on SET DATEFIRST and who knows what else
set @dowSat=datepart(dw,'20061028')
set @dowSun=datepart(dw,'20061029')
October 26, 2007 at 1:50 am
Sergiy (10/25/2007)
Are you sure Tom Carr is an author?Would you mind to look couple of pages back?
It's just almost exact copy of my script. "+6" is replaced with "-1" - that's all.
And if you search this forum you may find same formula in my post from somewhere 3 years ago.
So, you're clearly not the author.
Now now, Sergiy, give him some slack.
Clearly he's the author of the function.
By authoring a function doesn't also mean that the algorithm inside must be invented by the same person, now does it? 😛
/Kenneth
October 26, 2007 at 3:34 am
Tomm Carr (10/25/2007)
Here is my (hopefully) final submission.
Tomm,
I have checked it out and it seems to work just fine for everything I threw at it. Even though I'm not happy with the fact that it doesn't include the start day, I simply call you function subtrating 1 from the second argument.
That's why I think Jeff's AddWorkdays just doesn't need that third parameter. And that's why I think the fact that Jeff's Workdays function includes the start date is a great idea because if you don't want to include the start date you just subtract 1 from the returned result.
BUT, I still want to know from all those against including the start date in AddWorkdays or Workdays where in the real world this concept comes from. What does the start date represent? Is it a start date or is it the date before the start date?
October 26, 2007 at 7:25 am
Michael Meierruth (10/26/2007)
Tomm Carr (10/25/2007)
BUT, I still want to know from all those against including the start date in AddWorkdays or Workdays where in the real world this concept comes from. What does the start date represent? Is it a start date or is it the date before the start date?
Just my personal opinion, I do not think the start date should be included. For example, lets say I am asked to submit a report, or some other office gargage, and I am given two days to do it. If my boss does not tell me until 4:55 on Monday, then is it fair to say it is due on Tuesday? Technically, if you only look at the dates I had two days to do it, but really I had only one day. That is why I like Jeff's function, because everyone has their own oppinion.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 26, 2007 at 10:56 am
This is why I defined in an earlier posting that each date was to be considered the beginning of the work day. That way, if the date you are given is a Monday and you add one day to it, you arrive at Tuesday. Thus the code arrives at the same answer we would arrive at standing in front of a calendar and adding manually.
If you want to consider the time of day into the calculations, this presents an entire gaggle of other problems. Then you have to define the time of the start of the work day and the duration of the work day. 9 to 5? 8 to 5? 7:30 to 4:30? What about other shifts? What about the shift that starts at 11PM and ends at 8AM the next day?
As it is, the time portion of the reference date is unchanged. So if adding two work days to Monday at 4:55PM the answer you get back is Wednesday at 4:55PM. This keeps it shift and duration independent and thus should work for any environment.
What is somewhat confusing is what exactly is meant by "count the start day." If you add one day to a date you should get the next day. Stipulating for a moment that a date represents the start of the day, then we are counting the start day. We are now at the start of the next day ready to "consume" that day.
If you say you want to add one work day to Monday and get Monday back as an answer, show me on a calendar how this makes sense.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 26, 2007 at 11:20 am
Tomm Carr (10/26/2007)
If you say you want to add one work day to Monday and get Monday back as an answer, show me on a calendar how this makes sense.
If you want an answer to that, see my spec a few pages back. It makes sense out of adding a day to Monday that gives you Monday.
October 26, 2007 at 11:39 am
Here is another function that came out of this particular exercise:
/*
=============================================
Author:
Tomm Carr
Create date:
10/23/2007
Description:
Returns a 1 if the specified date occurs on a weekday (Mon-Fri) and 0 otherwise.
This function operates independently of the server's DateFirst setting because it uses the function DayOfWeek which is also DateFirst independent.
Examples:
select dbo.IsWeekDay ('10/20/2007') -- should be 0
select dbo.IsWeekDay ('10/21/2007') -- should be 0
select dbo.IsWeekDay ('10/22/2007') -- should be 1
select dbo.IsWeekDay ('10/23/2007') -- should be 1
select dbo.IsWeekDay ('10/24/2007') -- should be 1
select dbo.IsWeekDay ('10/25/2007') -- should be 1
select dbo.IsWeekDay ('10/26/2007') -- should be 1
=============================================
*/
ALTER FUNCTION [dbo].[IsWeekDay] (
@TargetDate Datetime
)
RETURNS bit
AS
BEGIN
return
case when dbo.DayOfWeek( @TargetDate ) in (1, 7)
then 0
else 1
end;
END--Function
Notice I left my name proudly attached. By golly, I wrote this!
Big whoop! One executable line of code. Can a function get any simpler? I've been a programmer for almost 25 years and I've generated thousands and thousands of lines of code. I've worked on some pretty hairy problems and developed some quite extensive algorithms to solve them, sometimes by myself and sometimes as part of a team. I can't even count the number of times I've helped someone solve a particularly vexing problem only to stand by while he gets all the atta-boys for work well done.
No biggy. I've collected my share of atta-boys during my career. I have a small box full of Certificates of Appreciation gathering dust somewhere out in my garage. I'm happy to help especially new grads just starting out. Rarely do I help someone and not learn something new. That's the payment I receive and its why I come here.
If I should step on someone's toes along the way, let me know and I will make appropriate amends. When I come across a thread that is already several pages long, I generally read the first page to get an idea of the problem and the last page to see if the problem has been solved already. I'm sure most of us here don't have the time to read every single post -- if you do, I want your job.:P
Anyway, I hereby render my apologies to Sergiy for any insult I may have given him. If he want to put his name on anything original I have posted here, he's welcome to it.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 26, 2007 at 12:07 pm
Michael Meierruth (10/26/2007)
If you want an answer to that, see my spec a few pages back. It makes sense out of adding a day to Monday that gives you Monday.
I don't want to seem demanding, but can you be a little more specific than "a few pages back"? I really, really don't have the time to browse through what is now 12 pages of posts.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 26, 2007 at 5:18 pm
Tomm Carr (10/26/2007)
Michael Meierruth (10/26/2007)
If you want an answer to that, see my spec a few pages back. It makes sense out of adding a day to Monday that gives you Monday.I don't want to seem demanding, but can you be a little more specific than "a few pages back"? I really, really don't have the time to browse through what is now 12 pages of posts.
Page 8
October 29, 2007 at 7:29 pm
Editor's Note: Reply censored by the Steve Jones
_____________
Code for TallyGenerator
Viewing 13 posts - 106 through 117 (of 117 total)
You must be logged in to reply to this topic. Login to reply