SELECT every date within range, excluding weekends

Hi there,

I am looking to write a query that returns all the dates between a pair of dates, but excludes dates that fall on either a saturday or sunday. Could anyone help me with where to start as I am a bit stumped. Thanks.

For example

Date1: 17/02/2011
Date2  24/02/2011

Result set:

17/02/2011
18/02/2011
21/02/2011
22/02/2011
23/02/2011
24/02/2011
LVL 2
wellsoAsked:
Who is Participating?
 
ong-hhConnect With a Mentor Commented:
DECLARE @StartDate DATETIME,@EndDate DATETIME

SELECT @StartDate = '20110101',@EndDate = '20110131'

SELECT DATEADD(day, z.num, @StartDate) AS CDate

FROM (

SELECT b10.i + b9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i num

FROM (SELECT 0 i UNION ALL SELECT 1) b0

CROSS JOIN (SELECT 0 i UNION ALL SELECT 2) b1

CROSS JOIN (SELECT 0 i UNION ALL SELECT 4) b2

CROSS JOIN (SELECT 0 i UNION ALL SELECT 8) b3

CROSS JOIN (SELECT 0 i UNION ALL SELECT 16) b4

CROSS JOIN (SELECT 0 i UNION ALL SELECT 32) b5

CROSS JOIN (SELECT 0 i UNION ALL SELECT 64) b6

CROSS JOIN (SELECT 0 i UNION ALL SELECT 128) b7

CROSS JOIN (SELECT 0 i UNION ALL SELECT 256) b8

CROSS JOIN (SELECT 0 i UNION ALL SELECT 512) b9

CROSS JOIN (SELECT 0 i UNION ALL SELECT 1024) b10

) z

WHERE z.num <= DATEDIFF(day, @StartDate, @EndDate)
aND  DATEPART(dw,DATEADD(day, z.num, @StartDate)) NOT IN (6,7)
ORDER BY z.num
0
 
Tomas ValentaIT ManagerCommented:
use function DATEPART (datepart, date) where datepart will be wd (weekday). See http://msdn.microsoft.com/en-us/library/ms174420.aspx
select ......where ..... AND (DATEPART(wd,datevalue) <> valueforSaturday  or DATEPART(wd,datevalue) <> valueforSunday)
0
 
Kobe_LenjouCommented:
select * from <table>
  where datepart(dw, <datefield>) not in (5,6)

Please also check the SET DATEFIRST statement since this can change the start of a week (sun / mon)

More info: http://msdn.microsoft.com/en-us/library/ms174420.aspx
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
wellsoAuthor Commented:
Hi folks,

I forgot to mention that the dates do not exist in a table, I was looking to genertate them with the SQL
0
 
Tomas ValentaIT ManagerCommented:
And what conditions are for dates ? You can use GETDATE() function to enter date data. Can you explain it more detailed ?
0
 
wellsoAuthor Commented:
The script gets two dates from a HTML form.

Date1: 17/02/2011
Date2  24/02/2011

These are passed to an SQL query, which returns all the dates between the two dates

Result set:

17/02/2011
18/02/2011
19/02/2011
20/02/2011
21/02/2011
22/02/2011
23/02/2011
24/02/2011

These dates are refined further to exclude the ones that fall on a a weekend

Result set:

17/02/2011
18/02/2011
21/02/2011
22/02/2011
23/02/2011
24/02/2011
0
 
Tomas ValentaIT ManagerCommented:
SELECT statement in SQL is working only with datas in SQL. I think the better for you is to do it in html formy by .NET or VBScript code.
If I understand you exactly then this is not possible to do in SQL. May be by procedure or function but I cannot see advantage to do it
on SQL server.
0
 
s_chilkuryCommented:
This can be achieved using CTE.

A Common Table Expression is a temporary result set that exists only within the scope of a single SQL statement.

with CTE(d)as(select d = convert(datetime,'20110217')

union all select d = d + 1 from CTE where d < '20110224')

SELECT d FROM CTE WHERE ((DATEPART(dw, d) + @@DATEFIRST) % 7) NOT IN (0, 1)

option (maxrecursion 370)

0
 
wellsoAuthor Commented:
Thanks buddy, works great
0
 
deightonCommented:
DECLARE @START AS datetime;
DECLARE @END AS datetime;

SET @START = '01-01-2011';
SET @END = '12-31-2021';

--YOU MIGHT NEED TO TWEAK THE IN DEPENDING ON SYSTEM SETTINGS

;with CTE (aDate)
AS
(
	SELECT @START as aDate
	UNION ALL
	SELECT DateADD(day,1,adate) FROM CTE WHERE adate < @END
)
SELECT 	*,DATEPART(weekday,adate) FROM CTE WHERE DATEPART(weekday,adate) NOT IN(7,1)
OPTION (MAXRECURSION 0);

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.