CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
angelcake
angelcake

Reputation: 119

How to get all Mondays or Tuesdays of Previous Month

How do I get all Mondays or Tuesdays of Previous Month? I haven't seen any example about it.

Upvotes: 1

Views: 1906

Answers (4)

Bettelbursche
Bettelbursche

Reputation: 443

WITH CTE_DATES 
AS(
    SELECT CAST(DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) -1, 0) AS DATE) AS [DATE] -- start previous month

    UNION ALL

    SELECT CAST(DATEADD(DD, 1, [DATE])  AS DATE)
    FROM [CTE_DATES]
    WHERE [DATE] < CAST(DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 0, 0)) AS DATE) -- end previous month
)

SELECT 
     [date] 
    ,DATENAME(WEEKDAY, [date]) AS 'Weekday'
    ,DATEPART(WEEKDAY, [date]) AS 'Day'
FROM [CTE_DATES]
WHERE DATEPART(WEEKDAY, [date]) IN (1, 2)

Upvotes: 0

shh
shh

Reputation: 91

declare @table table
(
   ID integer identity,
   DateRange date,
   Remark nvarchar(10),
   MonWeek nvarchar(1),
   weekgrp integer
)

declare @from Date
declare @newfrom Date
declare @to Date
declare @min integer
declare @max integer

set @from ='2016-03-01'
set @to = '2016-03-31'
set @newfrom = '2016-03-01'

while @from <= @to
begin
  insert into @table (DateRange, Remark) Values (@from,DATENAME(dw,@from))

  set @from = DATEADD(dd,1,@from)
end

update @table
set MonWeek = 'Y'
where Remark = 'Monday'

select @min = MIN(ID), @max = MAX(ID) from @table
where MonWeek = 'Y'

--to calculate week group
while @min <= @max
begin
   Update @table
   set weekgrp = @min
   where ID between @min and @min + 7
   set @min = @min + 7
end

select * from @table
where Remark in ('Monday','Tuesday')

Upvotes: 2

TharunRaja
TharunRaja

Reputation: 707

;WITH CTE (X)
 AS
 (
 SELECT DATEADD(MM,DATEDIFF(MM,0,GETDATE())-1,0)
 ),
 CTE2(N) AS
 (
 SELECT 0
 UNION ALL
 SELECT 1+N  FROM CTE2 WHERE N< (SELECT DATEDIFF(DD,DATEADD(MM,DATEDIFF(MM,0,GETDATE())-1,0),DATEADD(MM,1,DATEADD(MM,DATEDIFF(MM,0,GETDATE())-1,0))-1))
 )
 SELECT DATEADD(DD,N,X),DATENAME(DW,DATEADD(DD,N,X)) FROM CTE,CTE2 WHERE DATENAME(DW,DATEADD(DD,N,X)) IN ('Monday','Tuesday')

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You could use:

DECLARE @d DATE = GETDATE();

SELECT sub.prev_date
FROM (SELECT @d, MONTH(DATEADD(MM, -1, @d))) AS s(d,m)
CROSS APPLY (
    SELECT DATEADD(D, c-1, DATEADD(MM, -1, DATEADD(DD, 1 - DAY(d),d))) AS prev_date
    FROM (
    VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
           (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
           (21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31))AS x(c)
    ) AS sub
WHERE MONTH(sub.prev_date) = s.m 
  AND DATENAME(dw,sub.prev_date) IN ('Monday','Tuesday');

LiveDemo

Output:

╔════════════╗
║ prev_date  ║
╠════════════╣
║ 2016-03-01 ║
║ 2016-03-07 ║
║ 2016-03-08 ║
║ 2016-03-14 ║
║ 2016-03-15 ║
║ 2016-03-21 ║
║ 2016-03-22 ║
║ 2016-03-28 ║
║ 2016-03-29 ║
╚════════════╝

Warning:

SQL Server language should be English othewise DATENAME will not match.

You could also compare with DATEPART weekday but then you need to know SET DATEFIRST setting.

EDIT:

A bit shorter:

DECLARE @d DATE = '2015-01-01';

SELECT sub.prev_date
FROM (SELECT DATEADD(DD, c - DAY(@d),DATEADD(MM, -1, @d)) AS prev_date
      FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
                 (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
                 (21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31))AS x(c)) AS sub
WHERE MONTH(sub.prev_date) = MONTH(DATEADD(MM, -1, @d))
  AND DATENAME(dw,sub.prev_date) IN ('Monday','Tuesday');

LiveDemo

Upvotes: 2

Related Questions