I have a query getting the days in particular day that I selected and show the days in a month. My problem is that I need every two weeks of the month. And I also need to get long date format: dddd, dd MMMM yyyy
and eliminate the time. Please help me.
DECLARE @startDate DATETIME = '9/1/2019' DECLARE @endDate DATETIME = '12/31/2019' DECLARE @dayOfWeek INT = 1; WITH cte_Recursion AS ( SELECT @startDate AS [Date] UNION ALL SELECT DATEADD(DAY, 1, [Date]) FROM cte_Recursion WHERE [Date] < @endDate ) SELECT [Date] FROM cte_Recursion WHERE DATEPART(WEEKDAY, [Date]) = @dayOfWeek OPTION (MAXRECURSION 0)
The data returned looks like this:
2019-09-01 00:00:00.000 2019-09-08 00:00:00.000 2019-09-15 00:00:00.000 2019-09-22 00:00:00.000 2019-09-29 00:00:00.000 2019-10-06 00:00:00.000 2019-10-13 00:00:00.000 2019-10-20 00:00:00.000 2019-10-27 00:00:00.000 2019-11-03 00:00:00.000 2019-11-10 00:00:00.000 2019-11-17 00:00:00.000 2019-11-24 00:00:00.000
And I need to get like this (every other week):
2019-09-01 00:00:00.000 2019-09-15 00:00:00.000 2019-09-29 00:00:00.000 2019-10-13 00:00:00.000 2019-10-27 00:00:00.000 2019-11-10 00:00:00.000 2019-11-24 00:00:00.000
Advertisement
Answer
By changing your CTE to start on the correct day of the week, you can then change the recursion to add 14 days instead of 1, giving you the desired result without further manipulation:
DECLARE @startDate DATETIME = '9/1/2019' DECLARE @endDate DATETIME = '12/31/2019' DECLARE @dayOfWeek INT = 1; WITH cte_Recursion AS (SELECT DATEADD(DAY, ((@dayOfWeek - DATEPART(WEEKDAY, @startDate)) % 7 + 7) %7, @startDate) AS [Date] UNION ALL SELECT DATEADD(DAY, 14, [Date]) FROM cte_Recursion WHERE DATEADD(DAY, 14, [Date]) < @endDate) SELECT [Date] FROM cte_Recursion
Output:
Date 01/09/2019 00:00:00 15/09/2019 00:00:00 29/09/2019 00:00:00 13/10/2019 00:00:00 27/10/2019 00:00:00 10/11/2019 00:00:00 24/11/2019 00:00:00 08/12/2019 00:00:00 22/12/2019 00:00:00
To change the date format to dddd, dd MMMM yyyy
, simply replace the final SELECT
with:
SELECT FORMAT([Date], 'dddd, dd MMMM yyyy') AS [Date] FROM cte_Recursion
Output:
Date Sunday, 01 September 2019 Sunday, 15 September 2019 Sunday, 29 September 2019 Sunday, 13 October 2019 Sunday, 27 October 2019 Sunday, 10 November 2019 Sunday, 24 November 2019 Sunday, 08 December 2019 Sunday, 22 December 2019