Solved

SQL - Date difference excluding weekends

Posted on 2013-06-21
5
767 Views
Last Modified: 2013-06-24
Hello experts,

I have a need to calculate the number of business days between 2 dates and need to exclude business days.

I found this example, but it's not recognizing "TO_CHAR".

select sysdate - creation_dttm
- 2 * (to_char(sysdate, 'WW') - to_char(creation_dttm, 'WW'))
from the_table

Any ideas?
0
Comment
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 250 total points
ID: 39267212
From http://msdn.microsoft.com/en-us/library/ms189794(v=sql.90).aspx:

CREATE FUNCTION "fnGetBusinessDays"
(
@startdate datetime,
@enddate datetime
)
RETURNS integer
AS
BEGIN
DECLARE @days integer

SELECT @days = 
DATEDIFF(d,@startdate,@enddate) 
- DATEDIFF(wk,@startdate,@enddate) * 2 
- CASE 
WHEN DATENAME(dw, @startdate) <> 'Saturday' AND DATENAME(dw, @enddate) = 'Saturday' THEN 1 
WHEN DATENAME(dw, @startdate) = 'Saturday' AND DATENAME(dw, @enddate) <> 'Saturday' THEN -1 
ELSE 0
END
- (SELECT COUNT(*) FROM holidays WHERE bankholiday BETWEEN @startdate AND @enddate AND DATENAME(dw, bankholiday) <> 'Saturday' AND DATENAME(dw, bankholiday) <> 'Sunday')

RETURN (@days)

END
GO

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39267227
your tags say sql2005, but your sample syntax is for Oracle

so, here's an oracle sql method

  SELECT
           (TRUNC(SYSDATE) - TRUNC(creation_dttm))
         - NVL(
               (    SELECT COUNT(
                               CASE
                                   WHEN TO_CHAR(creation_dttm + LEVEL - 1, 'Dy') IN ('Sat', 'Sun')
                                   THEN  1                                
                               END
                           )
                      FROM DUAL
                CONNECT BY LEVEL < (TRUNC(SYSDATE) - TRUNC(creation_dttm))),
               0
           ) diff            
    FROM the_table
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 39267438
yep, it's confusing when the topics/tags say one thing but the query says another

so if SQL Server: here's a TSQL method without needing a function
SELECT
  ID
,  (DATEDIFF(dd, creation_dttm, getdate()) + 1)
  -(DATEDIFF(wk, creation_dttm, getdate()) * 2)
  -(CASE WHEN DATENAME(dw, creation_dttm) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
  as working_days
, <<other fierlds >>
from your_table

Open in new window

0
 

Author Comment

by:holemania
ID: 39272910
Sorry I saw an example and posted here.  Didn't know it was oracle, but yes I'm looking for SQL.  Thank you.
0
 

Author Closing Comment

by:holemania
ID: 39272911
Thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server query 12 32
add criteria to query in VB, Access 2003 2 32
sql update 2 38
SP converting date time to date and time separately 2 20
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question