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]
Go to Top of Page

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]
Go to Top of Page

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
Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-07 : 04:22:41
noted.
Go to Top of Page

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/
Go to Top of Page

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."
Go to Top of Page
   

- Advertisement -