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

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

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

Show all headers | View raw

On Monday, March 3, 2014 3:35:04 PM UTC-5, Andy wrote:
> Hi Everyone
>
> I have an odd question for SQL
>
> 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.
>
> StartDate = 03/14/2014 12:09:00pm
> EndDate = 03/17/2014 09:24:00am
>
> Or even more seemingly complex...
>
> StartDate = 03/14/2014 12:09:00pm
> EndDate = 03/24/2014 09:24:00am

The standard solution to this problem is to make use of a calendar
table. This is a table with a DATE primary key, and assorted other
columns, such is IS_WEEKEND or IS_FEDERAL_HOLIDAY. Such a table
occupies little space compared to real data, can be generated very
quickly, and makes complex date computations very simple.

http://stackoverflow.com/questions/5635594/how-to-create-a-calender-table-for-100-years-in-sql

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next 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