dateDiff question

Question

Post reply

dateDiff question

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 1

did you try the code in my previous post?

here is a slighly modified version - it seems to meet your requirements:

DECLARE @openDate DATETIME

DECLARE @closedDate DATETIME

SET @openDate = '4/19/2010'

SET @closedDate = '4/27/2010';

WITH dateCTE AS

(

SELECT @openDate AS theDate

UNION ALL

SELECT theDate + 1

FROM dateCTE

WHERE theDate + 1 < (@closedDate-1)

)

SELECT COUNT(theDate) AS no_of_working_days

FROM dateCTE

WHERE DATEPART(weekday,theDate) NOT IN (1,7)

The above returns 5 (days) on my machine

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

SQL_NuB (4/19/2010)
that is setup for the start date or end date begin on a weekend, I need to find out if the day between my two dates are a saturday or sunday and not count them
so if my openDate = 4/19/2010 and my closedDate = 4/27/10, I want to see 5 business days, the 19th isn't counted, saturday and sunday aren't counted and the 27th isn't counted, so I want to see 5 business days for those two dates, My open and close date will never be a weekend due to the frontend app won't allow it, however, a weekend can be in between the open and closed dates

Are you talking about the article I posted because, if you are, that's absolutely not what happens.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 3

Jeff Moden (4/19/2010)
SQL_NuB (4/19/2010)
that is setup for the start date or end date begin on a weekend, I need to find out if the day between my two dates are a saturday or sunday and not count them
so if my openDate = 4/19/2010 and my closedDate = 4/27/10, I want to see 5 business days, the 19th isn't counted, saturday and sunday aren't counted and the 27th isn't counted, so I want to see 5 business days for those two dates, My open and close date will never be a weekend due to the frontend app won't allow it, however, a weekend can be in between the open and closed dates
Are you talking about the article I posted because, if you are, that's absolutely not what happens.

yes, i entered in a weekday date, and ended it 5 days after including 2 weekend days, and the weekend days were counted. If I started on a weekend day, and ended on a weekday, my start date wasn't counted.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

SQL_NuB (4/21/2010)
Jeff Moden (4/19/2010)
SQL_NuB (4/19/2010)
that is setup for the start date or end date begin on a weekend, I need to find out if the day between my two dates are a saturday or sunday and not count them
so if my openDate = 4/19/2010 and my closedDate = 4/27/10, I want to see 5 business days, the 19th isn't counted, saturday and sunday aren't counted and the 27th isn't counted, so I want to see 5 business days for those two dates, My open and close date will never be a weekend due to the frontend app won't allow it, however, a weekend can be in between the open and closed dates
Are you talking about the article I posted because, if you are, that's absolutely not what happens.
yes, i entered in a weekday date, and ended it 5 days after including 2 weekend days, and the weekend days were counted. If I started on a weekend day, and ended on a weekday, my start date wasn't counted.

You need to go back and read the article. The 19th is intentionally included as a day. So is the 27th. That's the way the code was written. If you want to see 5 days instead of the 7 the code returns then, obviously, you need to change the code a bit. The original code looks like this...

DECLARE @StartDate DATETIME,

@EndDate DATETIME

SELECT @StartDate = '2010-04-19',

@EndDate = '2010-04-27'

SELECT

(DATEDIFF(dd, @StartDate, @EndDate) + 1)

-(DATEDIFF(wk, @StartDate, @EndDate) * 2)

-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

... and returns 7 days as it was designed to do. If you don't want it to include the end dates, then you need to change it...