Transact-SQL Cookbook: Help for Database Programmers by Ales Spetic & Jonathan Gennick

Author:Ales Spetic & Jonathan Gennick [Spetic, Ales]
Language: eng
Format: azw3
Tags: COMPUTERS / Database Management / General
ISBN: 9780596103637
Publisher: O'Reilly Media
Published: 2002-03-18T16:00:00+00:00

Excluding Recurrent Events

Problem

You have two dates, and you want to calculate the number of working days between them. This means that you must exclude Saturdays and Sundays from the calculation. For example, let’s calculate the number of working days between January 1, 2001 and March 1, 2001.

Solution

Use the following query, which makes use of the Pivot table and the DATEPART function to exclude weekend days:

SELECT COUNT(*) No_working_days, DATEDIFF(day,'2002-1-1','2002-3-1') No_days FROM Pivot WHERE DATEADD(day,i,'2002-1-1') BETWEEN '2002-1-1' AND '2002-3-1' AND DATEPART(weekday, DATEADD(d,i,'2002-1-1')) BETWEEN 2 AND 6

The query calculates the number of working and calendar days between the two dates:

No_working_days No_days --------------- ----------- 44 59

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.