Hi,

I need to write some TSQL that will return to me the total number of Mondays, tuesdays, wednesdays, thursdays, fridays, saturdays and sundays in a given date period. It needs to be as efficient as possible. What is the best way to do this?

I need to write some TSQL that will return to me the total number of Mondays, tuesdays, wednesdays, thursdays, fridays, saturdays and sundays in a given date period. It needs to be as efficient as possible. What is the best way to do this?

Of course if you need the best performances possible, take the code and put it directly in your SQL code instead of calling a funciton for each and every row

HTH

Hilaire

alternative not as nice as hilaire's :)

declare @startDate as smallDateTime

declare @endDate as smallDateTime

declare @dateReached as int

set @startDate = getDate()

set @endDate = getDate() + 10

set @dateReached = 0

print (@startDate)

print (@endDate)

--Create Temp Table

create table #weekday(dayOfWeek varchar(15), [count] int)

while(@dateReached = 0)

begin

if(datePart(dd, @startDate) = datePart(dd, @endDate))

begin

set @dateReached = 1

end

else

begin

declare @startDateDay as varchar(20)

set @startDateDay = dateName(dw, @startDate)

if exists (select 1 from #weekDay where dayOfWeek = @startDateDay)

begin

update #weekDay set [count] = [count] + 1 where dayOfWeek = @startDateDay

end

else

begin

insert #weekDay (dayOfWeek, [count]) values (@startDateDay, 1)

end

end

set @startDate = @startDate + 1

end

select * from #weekDay

drop Table #weekDay

Note that if you can insure that the @startDate is before @endDate, you can remove the ABS() and replace the "CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END" with "@startDate". IMO, it is dangerous to code ABS() and then always use the start date (@d1) in the second part of the calculation; if I were looking at the code, I would believe after seeing the ABS() that the code could handle either date coming first.

DECLARE @startDate DATETIME

DECLARE @endDate DATETIME

DECLARE @day INT

SET @startDate = '04/05/2004'

SET @endDate = '04/12/2004'

SELECT (ABS(DATEDIFF(DAY, @startDate, @endDate)) / 7) * CAST(01010101010101 AS BIGINT) +

CAST(CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 1 + @@DATEFIRST -

DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7

THEN '01' ELSE '00' END +

CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 2 + @@DATEFIRST -

DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7

THEN '01' ELSE '00' END +

CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 3 + @@DATEFIRST -

DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7

THEN '01' ELSE '00' END +

CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 4 + @@DATEFIRST -

DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7

THEN '01' ELSE '00' END +

CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 5 + @@DATEFIRST -

DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7

THEN '01' ELSE '00' END +

CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 6 + @@DATEFIRST -

DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7

THEN '01' ELSE '00' END +

CASE WHEN ABS(DATEDIFF(DAY, @startDate, @endDate)) % 7 >= (15 + 7 + @@DATEFIRST -

DATEPART(WEEKDAY, CASE WHEN @startDate <= @endDate THEN @startDate ELSE @endDate END)) % 7

THEN '01' ELSE '00' END AS BIGINT)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

returns int as

begin

return

abs(datediff(d, @d1, @d2)/7) -- 1 day per full week

+ case when abs(datediff(d, @d1, @d2)%7) >= (15+@day-@@datefirst-datep

end

go

--to count number of mondays between @date1 and @date2

-- use 2 instead of 1 to count tuesdays, and so on to 7 for sundays

select dbo.ufn_get_days(@date1, @date2, 1)

Hilaire