Skip to content
Advertisement

Get every 2 weeks on particular days SQL Server

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

Demo on dbfiddle

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

Demo on dbfiddle

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement