Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Get list of dates falling on Monday between two given dates Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, December 05, 2012 3:06 AM
 Grasshopper Group: General Forum Members Last Login: Monday, March 25, 2013 6:03 AM Points: 14, Visits: 25
 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
Post #1392874
 Posted Wednesday, December 05, 2012 3:31 AM
 SSCertifiable Group: General Forum Members Last Login: Friday, April 12, 2013 3:51 AM Points: 5,075, Visits: 4,831
 Look at building a calendar table, my personal favourite is this onehttp://www.sqlservercentral.com/scripts/Date/68389/Then you just need to query that table where the day is a Monday between your two dates.
Post #1392883
 Posted Wednesday, December 05, 2012 4:15 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, May 16, 2013 9:16 AM Points: 2,236, Visits: 6,486
 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 NFROM TALLYORDER BY N;`--EDIT--Sorry, there was a typo in the where clause. Fixed now. Not a DBA, just trying to learnFor 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/If you litter your database queries with nolock query hints, are you aware of the side effects?Try reading a few of these links...(*) Missing rows with nolock(*) Allocation order scans with nolock(*) Consistency issues with nolock(*) Transient Corruption Errors in SQL Server error log caused by nolock(*) Dirty reads, read errors, reading rows twice and missing rows with nolockLinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1392909
 Posted Wednesday, December 05, 2012 5:08 AM
 Grasshopper Group: General Forum Members Last Login: Monday, March 25, 2013 6:03 AM Points: 14, Visits: 25
 Many thanksRam
Post #1392927

 Permissions