Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

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

Show all headers | 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