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

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

X-Received	by 10.182.126.137 with SMTP id my9mr14895777obb.13.1393882721814; Mon, 03 Mar 2014 13:38:41 -0800 (PST)
X-Received	by 10.140.47.170 with SMTP id m39mr85516qga.19.1393882721773; Mon, 03 Mar 2014 13:38:41 -0800 (PST)
Path	csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!peer02.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!ur14no3916igb.0!news-out.google.com!bw18ni88qab.1!nntp.google.com!hw13no5433511qab.1!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Mon, 3 Mar 2014 13:38:41 -0800 (PST)
In-Reply-To	<23b8614b-5839-47ec-a647-37183eb2643d@googlegroups.com>
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=173.61.139.203; posting-account=SOVadwoAAAB3h7W1MLW9kMYtEc2JW2L8
NNTP-Posting-Host	173.61.139.203
References	<23b8614b-5839-47ec-a647-37183eb2643d@googlegroups.com>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<6ba9edb5-cfa7-494c-963a-5bc5fbd6c642@googlegroups.com> (permalink)
Subject	Re: Advanced DateDiff Calculation In SQL (exclude weekend time)
From	Ross Presser <rpresser@gmail.com>
Injection-Date	Mon, 03 Mar 2014 21:38:41 +0000
Content-Type	text/plain; charset=ISO-8859-1
X-Received-Bytes	2228
X-Received-Body-CRC	3467981381
Xref	csiph.com comp.databases.ms-sqlserver:1701

Show key headers only | 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