Q: How to find all weekends (Saturdays and Sundays) between two given dates?
This is a very commonly asked interview question. Lets write down the query using 3 different methods.
Know more about recursive CTE
DECLARE @fromD date = '2014-09-01', @toD date = '2014-09-30' ;WITH cteDate as ( SELECT ROW_NUMBER() OVER(ORDER BY NEWID()) as id, @fromD as dt, DATENAME(dw, @fromD) as dtName UNION ALL SELECT id + 1 as id, DATEADD(day, 1, dt), DATENAME(dw, DATEADD(day, 1, dt)) dtName FROM cteDate WHERE dt < @toD ) select dt, dtName from cteDate WHERE dtName in( 'Saturday', 'Sunday') OPTION(MAXRECURSION 0) -- Default max recusrsion = 100, this above option overrides the same
Using WHILE loop:
Declare @fromD datetime = '2014-09-01' Declare @toD datetime = '2014-09-30' Declare @cnt int = 0 DECLARE @table table (id int identity (1, 1), dt date, dtname varchar (100)) while @fromD < = @toD Begin if datename (weekday, @fromD) in ('Saturday', 'Sunday') INSERT INTO @table VALUES (@fromD, DATENAME(weekday, @fromD)) SET @fromD = DATEADD(D, 1, @fromD) END SELECT dt, dtname FROM @table
DECLARE @fromD DATE = '2014-09-01', @toD DATE = '2014-09-30' SELECT a.dt, DATENAME(dw, a.dt) AS dtName FROM (SELECT TOP (DATEDIFF(DAY, @fromD, @toD) + 1) dt = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY spt.name), DATEADD(DD, -1, @fromD)) FROM [master].[dbo].[spt_values] spt) a WHERE DATENAME(dw, dt) IN ('Saturday', 'Sunday')
Q: Find all dates between two dates?
It can be answered by tweaking up little bit of above queries..
There are ofcourse many other solutions to this question, so if you know any, then please share it up here in the comment section, I will add those into the current post with all credit.