asked on

SQL - Date difference excluding weekends

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?

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

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

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

holemania

ASKER

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

holemania

ASKER

Thanks.