Get list of dates falling on Monday between two given dates

  • Dear all,

    Can any one please help me providing the SQL query statement on getting the list of all dates that fall on 'Monday' between to given dates?

    Thanks in advance,

    Ram

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

    http://www.sqlservercentral.com/scripts/Date/68389/

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

  • 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/

  • Many thanks

    Ram

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

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