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

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

Newsgroups	comp.databases.ms-sqlserver
Date	2014-03-09 19:08 -0700
References	<23b8614b-5839-47ec-a647-37183eb2643d@googlegroups.com>
Message-ID	<0406603e-4048-4b67-ba0a-968b5f2db614@googlegroups.com> (permalink)
Subject	Re: Advanced DateDiff Calculation In SQL (exclude weekend time)
From	--CELKO-- <jcelko212@earthlink.net>

Show all headers | View raw

The Julian business day is a good trick. Number the days from whenever your 
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY, 
 julian_business_nbr INTEGER NOT NULL, 
 ...);

INSERT INTO Calendar 
VALUES ('2007-04-05', 42), 
 ('2007-04-06', 43), -- good Friday 
 ('2007-04-07', 43), 
 ('2007-04-08', 43), -- Easter Sunday 
 ('2007-04-09', 44), 
 ('2007-04-10', 45); --Tuesday


To compute the business days from Thursday of this week to next
 Tuesdays:

SELECT (C2.julian_business_nbr - C1.julian_business_nbr)
  FROM Calendar AS C1, Calendar AS C2
 WHERE C1.cal_date = '2007-04-05',
   AND C2.cal_date = '2007-04-10';

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