[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SELECT every date within range, excluding weekends

Posted on 2011-02-21
10
Medium Priority
?
2,642 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

650 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