dateDiff question

• Ivanna Noh

  SSCarpal Tunnel

  Points: 4138

  More actions

  April 19, 2010 at 1:25 pm

  #1154115

  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: 1004582

  More actions

  April 19, 2010 at 7:21 pm

  #1154233

  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

  April 21, 2010 at 5:07 am

  #1155248

  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: 1004582

  More actions

  April 24, 2010 at 6:20 pm

  #1157358

  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...

  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)

  If you want it to do something funky if the start or end date is on the weekends, then you'd need to make a change for that, as well. I'll let you have the pleasure of doing that.

  --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)