Get list of dates falling on Monday between two given dates

Question

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Look at building a calendar table, my personal favourite is this one

Then you just need to query that table where the day is a Monday between your two dates.

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

I agree with Anthony, a calendar table is the way to go.

If you can't create a calendar table for whatever reason, a slower way to solve the issue would be to do something like this: -

DECLARE @startDate DATE = '2012-01-01', @endDate DATE = '2012-12-31';

WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),

CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y),

CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y),

CTE4(N) AS (SELECT 1 FROM CTE3 x CROSS JOIN CTE3 y),

CTE5(N) AS (SELECT 1 FROM CTE4 x CROSS JOIN CTE4 y),

CTE6(N) AS (SELECT 0 UNION ALL

SELECT TOP (DATEDIFF(day,@startDate,@endDate))

ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

FROM CTE5),

TALLY(N) AS (SELECT DATEADD(day, N, @startDate)

FROM CTE6

WHERE DATENAME(weekday,DATEADD(day, N, @startDate)) = 'Monday')

SELECT N

FROM TALLY

ORDER BY N;

--EDIT--

Sorry, there was a typo in the where clause. Fixed now.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ramsai1973 Mr or Mrs. 500 Points: 564 More actions · Answer 3

Mr or Mrs. 500

Points: 564

December 5, 2012 at 5:08 am

Many thanks

Ram