Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting Weekdays date between a specified date range Expand / Collapse
Author
Message
Posted Monday, November 22, 2010 3:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 22, 2010 3:02 AM
Points: 31, Visits: 116
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....




Post #1024212
Posted Monday, November 22, 2010 3:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Try a calendar table

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




Clear Sky SQL
My Blog
Kent user group
Post #1024215
Posted Monday, November 22, 2010 4:31 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
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)

Post #1024237
Posted Monday, November 22, 2010 7:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:03 PM
Points: 2,262, Visits: 5,427
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
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL 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
Post #1024306
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse