Calculate Difference Between 2 Dates in SQL, Excluding Weekend Days

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 |

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 count date difference excluding weekend and holidays in MySQL

You might want to try this:

1. Count the number of working days (took it from here)

   SELECT 5 * (DATEDIFF('2012-12-31', '2012-01-01') DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY('2012-01-01') + WEEKDAY('2012-12-31') + 1, 1)

   This gives you 261 working days for 2012.

2. Now you need to know your holidays that are not on a weekend

   SELECT COUNT(*) FROM holidays WHERE DAYOFWEEK(holiday) < 6

   The result of this depends on your holiday table.

3. We need to get that in one query:

   SELECT 5 * (DATEDIFF('2012-12-31', '2012-01-01') DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY('2012-01-01') + WEEKDAY('2012-12-31') + 1, 1) - (SELECT COUNT(*) FROM holidays WHERE DAYOFWEEK(holiday) < 6)

   This should be it.

Edit: Please be aware that this only works properly if your end date is higher than your start date.

Calculate difference between two datetime excluding weekends

You can use mktime() to create UNIX timestamps for the two date/times you want to compare. These timestamps will represent the number of seconds between the Unix Epoch (January 1 1970 00:00:00 GMT) and the time specified. Since they will both be in seconds, it makes it very easy to calculate the seconds between the two timestamps:

<?php

//set start time and end time - mktime(hour, minute, second, month, day, year)
$startTime = mktime(15, 0, 0, 11, 2, 2018); // 2-11-2018 3:00PM
$endTime = mktime(11, 0, 0, 11, 5, 2018); // 5-11-2018 11:00AM

//calculate total number of seconds between two date/times
$totalSeconds = $endTime - $startTime;

//apply whatever other math you need...

?>

As far as accounting for weekends and business hours, you will need to get creative with determining how many weekend days exist between the two date/times and what hours fall within business hours on business days. The PHP manual for date functions will come in handy. The following code produces the results you are looking for:

<?php
//set business start and end hours
$businessStartHour = 10; //10 AM
$businessEndHour = 16; //4 PM

//set weekend days
$arrWeekendDays = array(6,0); //numeric representations of Saturday (6) and Sunday (0)

//set start and end dates and times
//2-11-2018 3 PM
$startHour = 15;
$startMinute = 0;
$startSecond = 0;
$startMonth = 11;
$startDay = 2;
$startYear = 2018;

//5-11-2018 11 AM
$endHour = 11;
$endMinute = 0;
$endSecond = 0;
$endMonth = 11;
$endDay = 5;
$endYear = 2018;

//create UNIX timestamps
$startTime = mktime($startHour, $startMinute, $startSecond, $startMonth, $startDay, $startYear);
$endTime = mktime($endHour, $endMinute, $endSecond, $endMonth, $endDay, $endYear);

//ensure $endTime is greater than $startTime
if($startTime >= $endTime){
    //invalid start and end datetimes
    die("Invalid start and end datetimes.");
}

//calculate eligible seconds from partial time on first and last day
$totalSeconds = 0;

$currentTime = mktime(0, 0, 0, $startMonth, $startDay, $startYear); //beginning of $startTime day
$lastFullDay = mktime(0, 0, 0, $endMonth, $endDay, $endYear); //beginning of $endTime day

$startingBusinessTime = mktime($businessStartHour, 0, 0, $startMonth, $startDay, $startYear);
$endingBusinessTime = mktime($businessEndHour, 0, 0, $endMonth, $endDay, $endYear);

if($startTime < $startingBusinessTime){
    $startTime = $startingBusinessTime;
}
if($endTime > $endingBusinessTime){
    $endTime = $endingBusinessTime;
}

if($currentTime == $lastFullDay){
    //$startTime and $endTime occur on the same day
    if($endTime > $startTime){
        $totalSeconds += ($endTime - $startTime);
    }
}else{
    //$startTime and $endTime do not occur on the same day
    $startingBusinessTime = mktime($businessStartHour, 0, 0, $endMonth, $endDay, $endYear);
    $endingBusinessTime = mktime($businessEndHour, 0, 0, $startMonth, $startDay, $startYear);
    if($endingBusinessTime > $startTime){
        $totalSeconds += ($endingBusinessTime - $startTime);
    }
    if($endTime > $startingBusinessTime){
        $totalSeconds += ($endTime - $startingBusinessTime);
    }
}

//calculate eligible seconds from all full days in between start day and end day
$fullDayBusinessSeconds = (($businessEndHour - $businessStartHour) * 3600);

//set $currentTime to beginning of first full day
$nextDay = $currentTime + (26 * 3600); //add 26 hours to $currentTime to get into the next day, compensating for possible daylight savings
$currentTime = mktime(0, 0, 0, date('n', $nextDay), date('j', $nextDay), date('Y', $nextDay));

while($currentTime < $lastFullDay){
    //determine if $currentTime is a weekday
    if(!in_array(date('w', $currentTime), $arrWeekendDays)){
        //it's a business day, add all business seconds to $totalSeconds
        $totalSeconds += $fullDayBusinessSeconds;
    }
    //increment $currentTime to beginning of next day
    $nextDay = $currentTime + (26 * 3600); //add 26 hours to $currentTime to get into the next day, compensating for possible daylight savings
    $currentTime = mktime(0, 0, 0, date('n', $nextDay), date('j', $nextDay), date('Y', $nextDay));
}

echo "Total eligible time between start time and end time: " . $totalSeconds . " seconds (" . convertSecToTime($totalSeconds) . ")";

function convertSecToTime($sec) 
 {
  $date1 = new DateTime("@0");
  $date2 = new DateTime("@$sec");
  $interval =  date_diff($date1, $date2);
  return $interval->format('%y Years, %m months, %d days, %h hours, %i minutes and %s seconds');
  // convert into Days, Hours, Minutes
  // return $interval->format('%a days, %h hours, %i minutes and %s seconds'); 
  }
?>

Count work days between two dates

For workdays, Monday to Friday, you can do it with a single SELECT, like this:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'

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 to include holidays, you have to work it out a bit...

Calculate the working hours between two dates in sql server excluding weekend

It's going to be a lot simpler to do this if you first create a calendar table, with one row per date. You can use that then to filter out weekends, and you will get one row as a result for each of the days when the booking is valid.

After that you just have to check:

• if the start date is the same as calendar date -> check possible hours to be deducted from the start
• if the end date is the same as calendar date -> check possible hours to be deducted from the end

Then just sum all other days with 8 hours, if that was your working day.

---

Related Topics