List all dates for a Monday within a date range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

List all dates for a Monday within a date range

Author

Topic

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2013-05-04 : 17:36:52

Hi

I’d like to create a temp table which simply lists all the dates for a Monday within a date range.

Thanks as always

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-04 : 22:37:09

Here is a way to generate the dates of all Mondays with in a date range:
[CODE]

CREATE TABLE #tmp11(MonDates Date);

declare @DateFrom Date
declare @DateTo Date

set @DateFrom ='2013-05-04'
set @DateTo = '2013-06-27'

INSERT INTO #tmp11(MonDates)
SELECT DATEADD(dd, number*7, DATEADD(dd, (7 - datediff(dd,0, @dateFrom)%7)%7, @dateFrom)) as MonDate
from master..spt_values where type = 'p' and number between 0 and datediff(dd, @dateFrom, @dateTo)/7
SELECT * from #tmp11;

DROP TABLE #tmp11

[/CODE]

OR

[CODE]
declare @DateFrom Date
declare @DateTo Date

set @DateFrom ='2013-05-04'
set @DateTo = '2013-06-27'

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')

[/CODE]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-07 : 02:31:51

[code]
declare @startdate datetime = '2013-05-04'
declare @enddate datetime = '2013-06-27'
;with cte(col)
as
(
select @startdate
union all
select col + 1
from cte
where col <= @enddate
)
select *, DATENAME(dw, col)
from cte
where DATEPART(dw, col) = 2
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-07 : 03:59:41

I prefer Waterducks methods as it is independent of any system tables. I'm always nervous to use tables like spt_values in production code as its implementation may change in future.
Only change i would have done will be to change the last filter as below


declare @startdate datetime = '2013-05-04'
declare @enddate datetime = '2013-06-27'
;with cte(col)
as
(
select @startdate
union all
select col + 1
from cte
where col <= @enddate
)
select *
from cte
where DATEDIFF(dd,0,col)% 7 = 0

to make it independent of DATEFIRST setting

http://visakhm.blogspot.in/2012/08/creating-server-independent-day.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-07 : 04:22:41

noted.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-07 : 05:30:10

or build a calendar table as far into the future you need dates for and index day of week column.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2017-06-21 : 22:36:14

quote:
Originally posted by visakh16

I prefer Waterducks methods as it is independent of any system tables. I'm always nervous to use tables like spt_values in production code as its implementation may change in future.
Only change i would have done will be to change the last filter as below

declare @startdate datetime = '2013-05-04'
declare @enddate datetime = '2013-06-27'
;with cte(col)
as
(
select @startdate
union all
select col + 1
from cte
where col <= @enddate
)
select *
from cte
where DATEDIFF(dd,0,col)% 7 = 0

to make it independent of DATEFIRST setting

http://visakhm.blogspot.in/2012/08/creating-server-independent-day.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I know this is an old post but don't use recursive CTEs that count/increment. They're slower than a while loop and use a lot more resources. Use Itzik Ben-Gan's cascading CTE method, instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources