Weekends - SQL Server Forums

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

Transact-SQL (2000)

Weekends

Author

Topic

eilob
Starting Member

6 Posts

Posted - 2007-03-20 : 11:20:35

Hi, need to know how to substract dates without weekends, for example if my date submitted is on Thursday 01/01/2007 and my publish date is on the Monday 05/01/2007 then my Turnaround is 2 days, which is the substraction of both dates without the weekends (with the weekends my turnaround will be 4).

Is there anyway I can get this using an sql statement???

Thanks!!!

Eileen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 11:40:09

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

Peter Larsson
Helsingborg, Sweden

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2007-03-20 : 20:40:17

I came up with this query some time ago, but I haven't thorougly tested it. It seems to work, but I didn't test all possible scenarios. Give it a shot and see if it works for you:


declare	@startdate	datetime,
	@enddate	datetime

set @startdate = convert(datetime, '20070301')
set @enddate = convert(datetime, '20070305')

SELECT 
DATEDIFF(	d, 
		DATEADD(	d, 
				CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 WHEN DATEPART(dw, @startdate) = 7 THEN 2 ELSE 0 END, 
				@startdate
			), 
		DATEADD(	d, 
				CASE WHEN DATEPART(dw, @enddate) = 1 THEN 1 WHEN DATEPART(dw, @enddate) = 7 THEN 2 ELSE 0 END, 
				@enddate
			)
	) 
- DATEDIFF(	wk, 
		DATEADD(	d, 
				CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 WHEN DATEPART(dw, @startdate) = 7 THEN 2 ELSE 0 END, 
				@startdate
			), 
		DATEADD(	d, 
				CASE WHEN DATEPART(dw, @enddate) = 1 THEN 1 WHEN DATEPART(dw, @enddate) = 7 THEN 2 ELSE 0 END, 
				@enddate
			)
	) * 2

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-21 : 00:16:02

There have been a lot of questions lately about counting workdays/weekend days.

Did everyone get the same homework assignment?

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 02:00:40

Do you remember last autumn when there all of a sudden where lots of questions about "people knowing each other"?
Not trees, but "I know A, A knows B and B knows C". How many steps are there between A and C?

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-21 : 02:10:26

What happen to all these professor ? Can't they set they own questions instead of copying from others ?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-21 : 02:21:35

it's a vicious cycle. lazy profs troll sqlteam to come up with exam questions.

lazy students too lazy to even search for answers, then they get asked here, again and again...

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources