sql find all mondays of year after todays date

Question

I have the following sql statement to find all Mondays dates of the year.

    SELECT DateAdd(week, 
                   o1.v + o0.v, 
                   DateAdd(day, 
                           2 - DatePart(dw, Convert(VARCHAR(4), 2012) + '-01-01'), 
                           Convert(VARCHAR(4), 2012) + '-01-01'
                          )
                  )
      FROM (SELECT  0 AS v UNION 
            SELECT  8 UNION 
            SELECT 16 UNION 
            SELECT 24 UNION 
            SELECT 32 UNION 
            SELECT 40 UNION 
            SELECT 48) AS o1
CROSS JOIN (SELECT 0 AS v UNION 
            SELECT 1 UNION 
            SELECT 2 UNION 
            SELECT 3 UNION 
            SELECT 4 UNION 
            SELECT 5 UNION 
            SELECT 6 UNION 
            SELECT 7) AS o0
     WHERE 2012 = DatePart(year, 
                           DateAdd(week, 
                                   o1.v + o0.v, 
                                   DateAdd(day, 
                                           2 - DatePart(dw, Convert(VARCHAR(4), 2012) + '-01-01'), 
                                           Convert(VARCHAR(4), 2012) + '-01-01')
                                  )
                          )

How can i modify it in order to find all monday dates after the todays date?

score 3 · Answer 1 · edited May 23 '17 at 12:19

3

I would create a calendar table rather than write a complex query. Then you can write a clear, simple query like this:

select 
    c.BaseDate
from 
    dbo.Calendar c
where 
    c.DayOfWeek = 'Monday' and
    c.YearNumber = year(getdate()) and
    c.BaseDate > getdate()

As a general rule, a calendar table is the easiest solution for working with dates because it is a lot simpler to query and maintain than functions, and you can add columns whenever you need to support a new date attribute.

edited May 23 '17 at 12:19

Community

1
1

answered Mar 08 '12 at 10:53

Pondlife

15,992
6
37
51

1

The best thing about calendar tables is that queries on them can be *seen* to be correct. Compare this query to one using date arithmetic in either SQL Server or Oracle. A hundred years in a calendar table is only 36k rows, which is practically nothing. – Mike Sherrill 'Cat Recall' Mar 08 '12 at 11:04

score 1 · Answer 2 · answered Oct 21 '16 at 20:01

there are many functions to help a long with CTE , it will be simple ,below some suggestions , hope it help .

declare @DateFrom Date 
declare @DateTo Date

set @DateFrom ='2016-01-01'
set @DateTo = '2016-12-31'

SELECT AllDates as MonDates from 
(Select DATEADD(d, number, @dateFrom) as AllDates from master..spt_values 
   where type = 'p' and number between 0 and datediff(dd, @dateFrom,   @dateTo)) AS D1    
WHERE DATENAME(dw, D1.AllDates)In('Monday')

score 0 · Answer 3 · answered Jan 09 '18 at 13:01

I used succesfully this query (i adapted one of the answers here)

SELECT MondaysThisMonth = cast (DATEADD(DAY,n,MondayBeforeFOM) as date)
FROM (
   SELECT FirstOfMonth, MondayBeforeFOM = DATEADD(DAY,DATEDIFF(DAY,0,FirstOfMonth)/7*7,0)
   FROM (SELECT FirstOfMonth = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)) d
) e
CROSS JOIN (SELECT 7 UNION ALL SELECT 14 UNION ALL SELECT 21 UNION ALL SELECT 28 UNION ALL SELECT 35) f (n)
WHERE DATEADD(DAY,n,MondayBeforeFOM) < DATEADD(MONTH,1,FirstOfMonth)

score 0 · Answer 4 · answered Mar 08 '12 at 10:38

you can do on the where but I think its quite complicated. A CTE is a good workaround:

with DAYS as (
  SELECT DateAdd(week, o1.v + o0.v, DateAdd(day, 2 - DatePart(dw
,  Convert(VARCHAR(4), 2012) + '-01-01'), Convert(VARCHAR(4)
,  2012) + '-01-01')) as MY_DAY
   FROM (SELECT 0 AS v UNION SELECT 8 UNION SELECT 16 UNION SELECT 24
      UNION SELECT 32 UNION SELECT 40 UNION SELECT 48) AS o1
   CROSS JOIN (SELECT 0 AS v UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
      UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) AS o0
   WHERE 2012 = DatePart(year, DateAdd(week, o1.v + o0.v, DateAdd(day
,     2 - DatePart(dw, Convert(VARCHAR(4), 2012) + '-01-01')
,     Convert(VARCHAR(4), 2012) + '-01-01')))
)
select MY_DAY from DAYS
where MY_DAY >getdate()

sql find all mondays of year after todays date

4 Answers4