Number of Days Between 2 Dates, Excluding Weekends

Calculate difference between 2 dates in SQL, excluding weekend days

You should try with a function :

CREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE)
RETURNS INT
RETURN ABS(DATEDIFF(date2, date1)) + 1
     - ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY),
                    ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2
     - (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1)
     - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7);

Test :

SELECT TOTAL_WEEKDAYS('2013-08-03', '2013-08-21') weekdays1,
       TOTAL_WEEKDAYS('2013-08-21', '2013-08-03') weekdays2;

Result :

| WEEKDAYS1 | WEEKDAYS2 |
-------------------------
|        13 |        13 |

Find day difference between two dates (excluding weekend days)

Maybe someone else can help you converting this function into JQuery's framework...

I found this function here.

function calcBusinessDays(dDate1, dDate2) { // input given as Date objects  var iWeeks, iDateDiff, iAdjust = 0;  if (dDate2 < dDate1) return -1; // error code if dates transposed  var iWeekday1 = dDate1.getDay(); // day of week  var iWeekday2 = dDate2.getDay();  iWeekday1 = (iWeekday1 == 0) ? 7 : iWeekday1; // change Sunday from 0 to 7  iWeekday2 = (iWeekday2 == 0) ? 7 : iWeekday2;  if ((iWeekday1 > 5) && (iWeekday2 > 5)) iAdjust = 1; // adjustment if both days on weekend  iWeekday1 = (iWeekday1 > 5) ? 5 : iWeekday1; // only count weekdays  iWeekday2 = (iWeekday2 > 5) ? 5 : iWeekday2;
  // calculate differnece in weeks (1000mS * 60sec * 60min * 24hrs * 7 days = 604800000)  iWeeks = Math.floor((dDate2.getTime() - dDate1.getTime()) / 604800000)
  if (iWeekday1 < iWeekday2) { //Equal to makes it reduce 5 days    iDateDiff = (iWeeks * 5) + (iWeekday2 - iWeekday1)  } else {    iDateDiff = ((iWeeks + 1) * 5) - (iWeekday1 - iWeekday2)  }
  iDateDiff -= iAdjust // take into account both days on weekend
  return (iDateDiff + 1); // add 1 because dates are inclusive}
var date1 = new Date("August 11, 2010 11:13:00");var date2 = new Date("August 16, 2010 11:13:00");alert(calcBusinessDays(date1, date2));

get DATEDIFF excluding weekends using sql server

Example query below, here are some details on how I solved it.

Using DATEDIFF(WK, ...) will give us the number of weeks between the 2 dates. SQL Server evaluates this as a difference between week numbers rather than based on the number of days. This is perfect, since we can use this to determine how many weekends passed between the dates.

So we can multiple that value by 2 to get the number of weekend days that occurred and subtract that from the DATEDIFF(dd, ...) to get the number of weekdays.

This doesn't behave 100% correctly when the start or end date falls on Sunday, though. So I added in some case logic at the end of the calculation to handle those instances.

You may also want to consider whether or not the DATEDIFF should be fully inclusive. e.g. Is the difference between 9/10 and 9/11 1 day or 2 days? If the latter, you'll want to add 1 to the final product.

declare @d1 datetime, @d2 datetime
select @d1 = '9/9/2011',  @d2 = '9/18/2011'

select datediff(dd, @d1, @d2) - (datediff(wk, @d1, @d2) * 2) -
       case when datepart(dw, @d1) = 1 then 1 else 0 end +
      case when datepart(dw, @d2) = 1 then 1 else 0 end

how to calculate number of days between two dates excluding weekend java

This will work for you

DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    Date date1 = df.parse("10/08/2013");
    Date date2 = df.parse("21/08/2013");
    Calendar cal1 = Calendar.getInstance();
    Calendar cal2 = Calendar.getInstance();
    cal1.setTime(date1);
    cal2.setTime(date2);

    int numberOfDays = 0;
    while (cal1.before(cal2)) {
        if ((Calendar.SATURDAY != cal1.get(Calendar.DAY_OF_WEEK))
           &&(Calendar.SUNDAY != cal1.get(Calendar.DAY_OF_WEEK))) {
            numberOfDays++;
        }
        cal1.add(Calendar.DATE,1);
    }
    System.out.println(numberOfDays);

Live Demo

Out put

7

Calculate the number of days between two dates excluding Weekends in powerbi using calculated column

The Problem was with the data type. They both needs to be on the same date type.

---

Related Topics