none
Business Days

    Question

  • I have a simple select statement in SQL.  I want to add a column to compare 2 date columns and do business days count, not just days.

    Column B 01/15/14 - Column A 01/05/14 = X days removing Saturday and Sunday.  Not worries about the holidays.

    How can this be done?

    Saturday, February 15, 2014 3:40 PM

Answers

  • Try the below:

    create table Test_Business(Startdate date, Enddate date)
    Insert into Test_Business values(GETDATE()-14,GETDATE()+13)
     
     
    SELECT  Startdate,EndDate,(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)
    From Test_Business
    
    Drop table Test_Business

    Saturday, February 15, 2014 4:47 PM

All replies