Groups > comp.databases.ms-sqlserver > #1709

Re: Advanced DateDiff Calculation In SQL (exclude weekend time)

X-Received	by 10.224.167.8 with SMTP id o8mr3772276qay.0.1394425930186; Sun, 09 Mar 2014 21:32:10 -0700 (PDT)
X-Received	by 10.140.107.138 with SMTP id h10mr613817qgf.2.1394425930173; Sun, 09 Mar 2014 21:32:10 -0700 (PDT)
Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!usenet.blueworldhosting.com!feeder01.blueworldhosting.com!peer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!w5no3023011qac.0!news-out.google.com!du2ni1227qab.0!nntp.google.com!w5no3023009qac.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Sun, 9 Mar 2014 21:32:09 -0700 (PDT)
In-Reply-To	<23b8614b-5839-47ec-a647-37183eb2643d@googlegroups.com>
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=188.30.14.7; posting-account=dELd-gkAAABehNzDMBP4sfQElk2tFztP
NNTP-Posting-Host	188.30.14.7
References	<23b8614b-5839-47ec-a647-37183eb2643d@googlegroups.com>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<ea1e3ca5-47ef-43af-bc70-f1cd8f6e6be0@googlegroups.com> (permalink)
Subject	Re: Advanced DateDiff Calculation In SQL (exclude weekend time)
From	rja.carnegie@gmail.com
Injection-Date	Mon, 10 Mar 2014 04:32:10 +0000
Content-Type	text/plain; charset=ISO-8859-1
X-Received-Bytes	2619
X-Received-Body-CRC	1408486433
Xref	csiph.com comp.databases.ms-sqlserver:1709

Show key headers only | View raw

On Monday, 3 March 2014 20:35:04 UTC, Andy  wrote:
> I'm trying to do a calculation on 2 date fields.  I'd like to 
> get the exact difference in time between 2 dates but I don't
> want to include any time if the duration falls over a weekend. 
> For example, find me the difference in time with the following
> but don't include any time after Friday at midnight to Sunday
> at midnight.

I once addressed a similar problem with a user-defined function,
but I gather that, as of SQL Server 2005 or thereabouts, there's
a high cost to using that.  Also, my requirement was to count
whole business-hours hours - or something like that.

If there is an overhead to using a function and you use it 
anyway, then there may be not much greater cost in just 
adding one day at a time to the first date until you pass
the second... or one week at a time, and then refine it.

There's probably something like that on some web page already,
but it may not take account of international variation in the
definition of "weekday" - and their names - or of the fact that 
the command "SET DATEFIRST" may have been used.  After all, 
that exists, presumably someone somewhere wanted to use it.

So, web pages that come up when you type "SQL Server" and
"business days" into Google - with the quote marks - 
that get into a discussion with visitors leaving comments,
is probably going to bring the function @@DATEFIRST into it,
basically just to cancel the setting out...

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

Advanced DateDiff Calculation In SQL (exclude weekend time) andy.mcvicker@siemens.com - 2014-03-03 12:35 -0800
  Re: Advanced DateDiff Calculation In SQL (exclude weekend time) Ross Presser <rpresser@gmail.com> - 2014-03-03 13:38 -0800
  Re: Advanced DateDiff Calculation In SQL (exclude weekend time) --CELKO-- <jcelko212@earthlink.net> - 2014-03-09 19:08 -0700
  Re: Advanced DateDiff Calculation In SQL (exclude weekend time) --CELKO-- <jcelko212@earthlink.net> - 2014-03-09 19:08 -0700
  Re: Advanced DateDiff Calculation In SQL (exclude weekend time) rja.carnegie@gmail.com - 2014-03-09 21:32 -0700

csiph-web