Number of Mondays between two dates

Question

Number of Mondays between two dates

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Andrey Right there with Babe Points: 780 More actions · Answer 1

declare @dt1 date  = '20190501';
declare @dt2 date  = '20190522';

with dates as
(
select @dt1 [d], datename(dw,@dt1) [n]
union all
select dateadd(dd,1,d),datename(dw,dateadd(dd,1,d)) 
from dates 
where d<=@dt2
)
select 
count(1) [Mondays]
from dates 
where n='Monday'

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Look at the progress we've got in SQL over the years!

what used to be 13 years ago a short inline formula using system functions and integer arithmetic is now implemented with a recursive CTE combined with a string comparison in WHERE clause, which adds huge computing overheads caused by collation rules.

No wonder no server is big enough nowadays for a data warehouse of a size larger than an Excel spreadsheet. And no wonder MS are making so much money from Azure service - every solution like the one above adds a good buck to their bill, and man - aren't they all over the places?

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 3

Sergiy wrote:

Look at the progress we've got in SQL over the years! what used to be 13 years ago a short inline formula using system functions and integer arithmetic is now implemented with a recursive CTE combined with a string comparison in WHERE clause, which adds huge computing overheads caused by collation rules. No wonder no server is big enough nowadays for a data warehouse of a size larger than an Excel spreadsheet. And no wonder MS are making so much money from Azure service - every solution like the one above adds a good buck to their bill, and man - aren't they all over the places?

Oh lordy! After reading that, I almost killed myself by trying to hold it in. Gotta let it go!

BWAAAAA-HAAAAAA-HAAAAA-HAAAAA! HOOOOOOOOOIEEEEEEEEE!!!! ROFLMAO!!!!

Thanks for the laugh, Sergiy! Needed it especially after this migration thing they did.

--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)

vladdraco Newbie Points: 1 More actions · Answer 4

I did sth like below to count Mondays in a month where the month is calculated from the date passed as parameter to the function:

CREATE FUNCTION fn_GetNumMondaysInMonth (@Date datetime) RETURNS INT AS

Begin

DECLARE

@StartDate datetime,

@EndDate datetime,

@count int

SET @StartDate = DATEADD(DAY, 1, EOMONTH(@Date, -1))

SET @EndDate = EOMONTH(@Date)

SET @count = 0

While @StartDate != @EndDate

Begin

Declare @a int

set @a = datepart(dw,@StartDate)

if @a =2

begin

set @count = @count + 1

end

set @StartDate = DateAdd(Day,1,@startdate)

end

RETURN @count

end

Cheers 🙂

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 5

Sergiy wrote:

Look at the progress we've got in SQL over the years!
what used to be 13 years ago a short inline formula using system functions and integer arithmetic is now implemented with a recursive CTE combined with a string comparison in WHERE clause, which adds huge computing overheads caused by collation rules.
No wonder no server is big enough nowadays for a data warehouse of a size larger than an Excel spreadsheet. And no wonder MS are making so much money from Azure service - every solution like the one above adds a good buck to their bill, and man - aren't they all over the places?

And it's not over yet. Look at the latest function in the post above this one.

--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)

DesNorton SSC-Insane Points: 24374 More actions · Answer 6

Jeff Moden wrote:

Sergiy wrote:
Look at the progress we've got in SQL over the years!
what used to be 13 years ago a short inline formula using system functions and integer arithmetic is now implemented with a recursive CTE combined with a string comparison in WHERE clause, which adds huge computing overheads caused by collation rules.
No wonder no server is big enough nowadays for a data warehouse of a size larger than an Excel spreadsheet. And no wonder MS are making so much money from Azure service - every solution like the one above adds a good buck to their bill, and man - aren't they all over the places?
And it's not over yet. Look at the latest function in the post above this one.

Not to mention datepart(dw,@StartDate), which is dependent on DATEFIRST

How to post data/code on a forum to get the best help.