Getting Weekdays date between a specified date range

Question

Post reply

Getting Weekdays date between a specified date range

vibin.das

Say Hey Kid

Points: 705
More actions
November 22, 2010 at 3:02 am

#229447

Hi All,
Following is my requirement.
I have a start date and end date For Ex-
@StartDate = 2010-11-22 and @EndDate = 2010-12-22
I need to get all dates for Monday,similarily for other days within the above date range
ie.For Monday it should return
2010-11-29,2010-12-06,2010-12-13,2010-12-20
Similarily for Tuesday it should return
2010-11-30,2010-12-07,2010-12-14,2010-12-21
Can anybody help me out on this....

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 1

Try a calendar table

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Clear Sky SQL
My Blog[/url]

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 2

OR something like this

declare @StartDate datetime = '2010-11-22'

declare @EndDate datetime = '2010-12-22'

;with DateSequence

as

(

select @StartDate Date

union all

select Date+1 from DateSequence where Date<@EndDate

)

select *,datename(WEEKDAY,Date)Day from DateSequence

order by datepart(WEEKDAY,Date)

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 3

How about this?

DECLARE @StartDate DATETIME --= '2010-11-22'

DECLARE @EndDate DATETIME --= '2010-12-22'

SET @StartDate = '2010-11-22'

SET @EndDate = '2010-12-22'

; WITH Tens (N) AS

(

SELECT 1

UNION ALL SELECT 1

),

Thousands (N) AS

(

SELECT T1.N FROM Tens T1 , Tens T2 , Tens T3

),

TallyOnTheFly (N) AS

(

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM Thousands

)

SELECT DATEADD(DD ,( N-1 ), @StartDate ) DateCol

FROM TallyOnTheFly

WHERE DATEDIFF(DD ,@StartDate , @EndDate) >= ( N - 1 )

You can extend the TallyOntheFly to any number of your wish 😉