Solved

SELECT every date within range, excluding weekends

Posted on 2011-02-21
10
2,241 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
 
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now