Difference Between Two Dates... But Excluding Satureday and Sunday.

Hi Friendz,

When I was newbie in SQL, issue came in report from one project. Client want to calculate the difference between 2 dates (in hours) but excluding Saturday and Sunday. 

The default DateDiff function in SQL Server is easy to use but it gives clear difference. 

Syntax :

Datediff(datepart , start_date , end_date)

Way to use DateDiff function :

select DateDiff(minute, '2009-12-31 23:59:59.9999999', '2010-01-01 00:00:00.0000000')

For more details visit :

http://msdn.microsoft.com/en-us/library/ms189794.aspx

But for the above problem is not useful.

Definitely we need to create function which fulfills our requirement.

So here you go..

Create the following function in Database.

Create Function dbo.fn_GetHoursExcludeSatSun(@FromDate DateTime,@ToDate DateTime)
Returns bigint
As
Begin

 Declare @ldt   datetime;   
 Declare @dtdiff bigint;
 Declare @cnt  bigint; 
 Declare @Thrs  bigint; 
 Declare @Thrs1  bigint; 
 Declare @frmhrs  bigint;
 Declare @tohrs  bigint;
 Declare @tmpFrmDate   datetime;  

  if DateName(dw,@FromDate) ='Saturday' or DATENAME(dw,@FromDate) ='Sunday'
  begin
      Set @frmhrs = 0
  end
  else
   begin
     Set @frmhrs = DateDiff(hh,convert(datetime,convert(varchar(10),@FromDate,101)),@FromDate)
   end

   if DateName(dw,@TODate) ='Saturday' or DATENAME(dw,@TODate) ='Sunday'
   begin
     Set @tohrs = 0
   end
   else
     begin
       Set @tohrs = DateDiff(hh,convert(datetime,convert(varchar(10),@TODate,101)),@TODate)
     end

    set @Thrs=0
    set @dtdiff=0
    set @cnt=1
 

    select @dtdiff=datediff(dd,convert(datetime,@FromDate),convert(datetime,@ToDate))
 

   set @dtdiff=@dtdiff
   set @ldt=convert(datetime,@FromDate)
   set @tmpFrmDate=convert(datetime,@FromDate)
 

        While (@cnt<=@dtdiff)
        Begin
          if DateName(dw,@ldt) ='Saturday' or DATENAME(dw,@ldt) ='Sunday'
          begin
               set @Thrs1=1
          end
        else
        begin
              set @Thrs = @Thrs + datediff(hh,@ldt,DATEADD(dd,@cnt,@FromDate))
       end
 

       select @ldt=DATEADD(dd,@cnt,@FromDate)  
      set @cnt=@cnt+1
   end 
    set @Thrs = @Thrs - @frmhrs
    set @Thrs = @Thrs + @tohrs
  Return   @Thrs
End

How to use this function :

select dbo.fn_GetHoursExcludeSatSun(First_Date,Second_Date) 

It is possible to use the column names (with datatype 'datetime') instead of First_Date, Second_Date.