Solved

# SQL - Date difference excluding weekends

Posted on 2013-06-21
769 Views
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
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

LVL 40

Accepted Solution

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
``````
0

LVL 74

Expert Comment

ID: 39267227

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 49

Assisted Solution

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
``````
0

Author Comment

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

ID: 39272911
Thanks.
0

## Featured Post

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
###### Suggested Courses
Course of the Month5 days, 10 hours left to enroll