Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get list of dates falling on Monday between two given dates Expand / Collapse
Author
Message
Posted Wednesday, December 05, 2012 3:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

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




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1392883
Posted Wednesday, December 05, 2012 4:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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 N
FROM TALLY
ORDER BY N;

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



Not a DBA, just trying to learn

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/



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 nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1392909
Posted Wednesday, December 05, 2012 5:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 25, 2013 6:03 AM
Points: 14, Visits: 25

Many thanks
Ram
Post #1392927
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse