Day of Week Function
-
Hi all,
I'm doing some ErlangC calculations and need to determine the number of particular "Day of Week"s there are between two dates. I would presume to use the DatePart(dw,Date) function, but not quite sure what is the best way to set up the function to do this. I only need a return of the number of days.
For Instance, between 9/26/06 and 10/3/06, if I chose Tuesday, I would receive back 2, otherwise for any other DayofWeek choice, I would get 1.
Thanks
-
This is the function I created. Is there a better way to do this?
CREATE FUNCTION GetDayofWeekCount (@startdate datetime, @EndDate datetime, @DayofWeek int)
RETURNS Int AS
BEGIN
Declare @days int
Set @days = 0
while @startdate <= @enddate
Begin
if datepart(dw,@startdate) = @dayofweek
Begin
Set @days = @days + 1
End
set @startdate = dateadd(d,1,@startdate)
End
return @days
END
-
I tried to calculate your function with:
SELECT @DayDiff = DATEDIFF(DAY,@startdate,@EndDate)
and then dividing by 7 (week days) and ceiling etc.
My code looks "cleaner" but when I checked performance it looks the same.
My suggestion for you is to keep it as you posted --> your code
-
Thanks.
-
Yep... this is about 3 times faster (I tested both)...
CREATE FUNCTION dbo.GetDOWCount
(
@StartDate DATETIME,
@EndDate DATETIME,
@DOW VARCHAR(9) --Monday, Tuesday, Wednesday, etc
)
RETURNS INT
AS
BEGIN
RETURN (SELECT COUNT(*)
FROM dbo.Tally t
WHERE t.N <= DATEDIFF(dd,@StartDate,@EndDate)+1
AND DATENAME(dw,t.N+@StartDate-1) = @Dow)
END
It takes the spelled out days of the week so you don't have to worry about the @@DateFirst setting.
Here's an alternate where you need to worry about the @@DateFirst setting but it IS about 5 times faster than the function you posted...
CREATE FUNCTION dbo.GetDOWCount
(
@StartDate DATETIME,
@EndDate DATETIME,
@DOW INT -- 1,2,3,4,5,6, or 7 and is DateFirst sensitive
)
RETURNS INT
AS
BEGIN
RETURN (SELECT COUNT(*)
FROM dbo.Tally t
WHERE t.N <= DATEDIFF(dd,@StartDate,@EndDate)+1
AND DATEPART(dw,t.N+@StartDate-1) = @Dow)
END
If you don't already have a "Tally" table, you'll find that it has dozens of such uses and now is the time to make one... here's how...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
You could get even better performance if you created an "Auxilary Date Table" but most DBA's can't be convinced that it's worth the space... I think it is...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Hey Jeff, thanks for the reply and terrific solution.
I have created the tally table and the functions. Either works well.
And, it does run fast!!!
Thanks again for a new way a getting answers!
-
Thanks for the feedback, Steve.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply