?
Solved

SELECT every date within range, excluding weekends

Posted on 2011-02-21
10
Medium Priority
?
2,781 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…
Suggested Courses

840 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