datetime diference without weekend

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

datetime diference without weekend

Author

Topic

Lady
Starting Member

32 Posts

Posted - 2003-06-11 : 05:16:36

How shell I found difference between 2 date except weekend days?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-06-11 : 05:57:58

Hi

Basically you need a table with dates that aren't weekend days. Then you can count the dates that fall in your date range.

Because I am an ultra nice person, I did it for you



Create Table weekdays (date datetime not null)

GO

SET NOCOUNT ON

declare @val datetime
select @val = '1-JAN-2003'
while @val <= '1-DEC-2003'
begin
	
	IF DateName(dw, @val) NOT in ('saturday', 'sunday')
	BEGIN
	
		Insert into weekdays values (@val)
	END
	select @val = DATEADD(d, 1, @val)
end

SET NOCOUNT OFF

That is the table. Now the query. You might need to tweak it depending on if you want your days inclusive or not.



Declare @Date1 datetime,
	@Date2 datetime


Select @Date1 = '2-jun-2003', @Date2 = '11-jun-2003'



SELECT Count(*) 
FROM
	weekdays
WHERE
	[date] Between @Date1 and @Date2

How is that ?

Damian

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-06-11 : 08:42:01

Couldn't you also just find the difference in days and subtract 2 * the difference in weeks??

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-11 : 08:47:18

Psst.... Valter!

I think Merkin was showing off for Lady!

Sam

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-06-11 : 08:55:58

What if one of the given dates was a saturday ?
I.E. Select DateDiff(ww, '1-jun-2003', '16-jun-2003') gives you 2. But there are 3 weekend days in there.

This method also allows you to remove public holidays from your weekday table.

Damian

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-06-11 : 09:12:33

The first part only works if it falls on a weekend day I will continue to work it later but i'm pretty sure you can do it all in a formula.

SELECT -DatePart(dw,'20030601')%7 +
DateDiff(ww,DateAdd(dd,-1,'20030601'),'20030616')*2

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-11 : 09:18:15

quote:

I think Merkin was showing off for Lady!

I thought that was Justin's Job....

Brett

8-)

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-06-11 : 09:23:18

quote:

The first part only works if it falls on a weekend day I will continue to work it later but i'm pretty sure you can do it all in a formula.

SELECT -DatePart(dw,'20030601')%7 +
DateDiff(ww,DateAdd(dd,-1,'20030601'),'20030616')*2

What about public holidays ?

Damian

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-11 : 13:21:26

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22550

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-06-11 : 23:09:43

Jeff,

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-11 : 23:18:47

just realized what i posted doesn't answer the question at all, but is a related problem I guess!

(somewhere else I did post the formula for the question asked but I couldn't find it ....)

- Jeff

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-06-11 : 23:50:47

This looks like a job for the Numbers table....


declare @StartDate datetime; 
declare @EndDate datetime;

Select @StartDate = '20030609', @EndDate = '20030727'

Select COUNT(*)
from Numbers
WHERE Number <= DATEDIFF(d,@StartDate, @EndDate)
AND DATEPART(dw,DATEADD(d,Number,@StartDate)) NOT IN (1,7)

The NOT IN values will be based on SET DATEFIRST settings...

EDIT: I was thinking about this and thought surely using Valter forumale as a start it should be possible...


SELECT (DATEDIFF(d,@StartDate,@EndDate) + 1) - (2 * DATEDIFF(wk,@StartDate, @EndDate))
	- CASE WHEN DATEPART(dw, DATEADD(d, @@DATEFIRST, @StartDate)) = 1 THEN 1 ELSE 0 END
	- CASE WHEN DATEPART(dw, DATEADD(d, @@DATEFIRST, @EndDate)) = 7 THEN 1 ELSE 0 END

DavidM

"SQL-3 is an abomination.."

Edited by - byrmol on 06/12/2003 00:51:03

Peter.Bij@klm.com
Starting Member

12 Posts

Posted - 2003-06-13 : 11:48:22

Alternative:
put all dates to be dismissed in a table (e.g. weekends and Holidays)
then substract the number of days_to_be_dismissed which fall in selected period of time.

Just a thought, no coding (yet)

greetings, Peter

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-13 : 14:19:42

Here's the one I meant to refer to:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21594

that uses a simple forumla, and also shows you how to incorporate a table of holidays if you wish.

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-13 : 14:32:27

Mr. Cross Join,

Select StartDate, EndDate, (RawDays - NumWeeks * 2 + (CASE WHEN StartWD = 1 THEN 1 ELSE 0 END) - (CASE WHEN EndWD = 7 THEN 1 ELSE 0 END)) AS NumberOfWorkingDays
FROM
(Select StartDate, EndDate, Datepart(dw,StartDate) as StartWD,
Datepart(dw,EndDate) as EndWD,
DateDiff(d,StartDate,@EndDAte) as RawDays,
DateDiff(wk,StartDAte,@EndDate) as NumWeeks
FROM Dates
) A

OK. Looks like Rawdays, minus all Saturday and Sundays, Plus 1 if the StartWD is Sunday, Minus 1 if EndWD is Saturday.

First blush, this must have something to do with the way WEEKS is calculated.

Can you explain this ?

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources