Solved

SELECT every date within range, excluding weekends

Posted on 2011-02-21
10
2,265 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:wellso
10 Comments
 
LVL 9

Expert Comment

by:Tomas Valenta
ID: 34941195
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
 
LVL 8

Expert Comment

by:Kobe_Lenjou
ID: 34941200
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
 
LVL 2

Author Comment

by:wellso
ID: 34941207
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 9

Expert Comment

by:Tomas Valenta
ID: 34941242
And what conditions are for dates ? You can use GETDATE() function to enter date data. Can you explain it more detailed ?
0
 
LVL 2

Author Comment

by:wellso
ID: 34941443
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
 
LVL 9

Expert Comment

by:Tomas Valenta
ID: 34941511
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
 
LVL 9

Expert Comment

by:s_chilkury
ID: 34941513
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
 
LVL 4

Accepted Solution

by:
ong-hh earned 500 total points
ID: 34941515
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
 
LVL 2

Author Closing Comment

by:wellso
ID: 34941519
Thanks buddy, works great
0
 
LVL 18

Expert Comment

by:deighton
ID: 34941594
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question