CODEWITHSUNDEEP
sqlsql-server
Kervy Hipona
Kervy Hipona

Reputation: 21

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

Upvotes: 0

Views: 2384

Answers (3)

Nick
Nick

Reputation: 147216

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

Upvotes: 2

Ilyes
Ilyes

Reputation: 14928

You could use a recursive CTE as

WITH CTE AS
(
  SELECT  '2019-09-01 00:00:00.000' ADate
  UNION SELECT '2019-09-08 00:00:00.000'
  UNION SELECT '2019-09-15 00:00:00.000'
  UNION SELECT '2019-09-22 00:00:00.000'
  UNION SELECT '2019-09-29 00:00:00.000'
  UNION SELECT '2019-10-06 00:00:00.000'
  UNION SELECT '2019-10-13 00:00:00.000'
  UNION SELECT '2019-10-20 00:00:00.000'
  UNION SELECT '2019-10-27 00:00:00.000'
  UNION SELECT '2019-11-03 00:00:00.000'
  UNION SELECT '2019-11-10 00:00:00.000'
  UNION SELECT '2019-11-17 00:00:00.000'
  UNION SELECT '2019-11-24 00:00:00.000'
),
F AS
(
  SELECT CAST('2019-09-01 00:00:00.000' AS DATETIME) ADate
  UNION ALL
  SELECT DATEADD(Day, 14, F.ADate)
  FROM F
  WHERE ADate < '2019-11-24 00:00:00.000'
)
SELECT *
FROM F;

Upvotes: 1

nimajv
nimajv

Reputation: 433

you can use it :

 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 
   and DATEPART(WEEK, [Date]) % 2 = 0
   OPTION (MAXRECURSION 0)

Upvotes: 1

Related Questions