How can I change this code to a SQL 2012 function?

Question

 declare @startTime datetime
,@endTime datetime
, @IntervalIncWeekends int ;
set @startTime = '03/13/2017 11:00:02'
set @endTime = '03/30/2017 19:53:59'
IF DATENAME(dw, @startTime) = 'Saturday'
SET @startTime = CAST(CAST(DATEADD(dd,2,@startTime) AS DATE) AS 
DATETIME); 
IF            DATENAME(dw, @startTime) = 'Sunday'
SET  @startTime = CAST(CAST(DATEADD(dd,1,@startTime) AS DATE) AS 
DATETIME); 
IF DATENAME(DW, @endTime) = 'Saturday'
SET    @endTime = DATEADD(MI,-1, CAST(CAST(@endTime AS DATE) 
AS DATETIME));
IF DATENAME(DW, @endTime) = 'Sunday'
SET @endTime = DATEADD(MI,-1, CAST(CAST(DATEADD(dd,-1,@endTime) AS 
DATE) AS DATETIME));

IF @endTime <= @startTime
BEGIN
    SELECT 0;
END
ELSE
BEGIN
DECLARE       @NumberofWeekendDays decimal(5,2)  = 0;
 ;WITH cte as (
   SELECT CAST(DATEADD(DD,1,@startTime) AS DATE) AS dt
                           WHERE  DATEADD(DD,1,@startTime) < @endTime
                           UNION  ALL
                          SELECT DATEADD(DD,1,dt)
                           FROM   cte
                           WHERE  DATEADD(DD,1,dt) < @endTime
              )
              SELECT @NumberofWeekendDays = COUNT(*)
              FROM   cte
              WHERE  DATENAME(dw, dt) IN ('Saturday','Sunday');
                            SELECT ((DATEDIFF(MI,@startTime,@endTime) - 
(@NumberofWeekendDays * 60 * 24))/60)/24 m;
    END

How can I change this to a sql function so that I can pass 2 dates as parameter to the function, and get a time return inserted?

Answer 1

Here is an option which can be turned into a Table-Valued Funcion or even a Scalar-Valued Function. My strong recomendation would be a TVF.

You also have the added option of excluding Holidays. (currently listed as values, but could be a table as well).

Example For a Quick Test

Declare @D1 datetime ='2017-03-13 11:00:02'
Declare @D2 datetime ='2017-03-30 19:53:59'

Select Count(*)/60.0/24.0
 From  (
        Select Top (DateDiff(MINUTE,@D1,@D2)) 
               D=DateAdd(MINUTE,-1+Row_Number() Over (Order By (Select NULL)),@D1) 
         From  master..spt_values N1,master..spt_values N2
       ) N
 Where Datename(WeekDay,D) Not In ('Saturday','Sunday')
   and D Not In ( '2016-01-01' -- New Year's Day
                 ,'2016-01-18' -- Martin Luther King, Jr,
                 ,'2016-02-15' -- Washington''s Birthday
                 ,'2016-03-25' -- Good Friday
                 ,'2016-05-30' -- Memorial Day
                 ,'2016-07-04' -- Independence Day
                 ,'2016-09-05' -- Labor Day
                 ,'2016-11-24' -- Thanksgiving
                 ,'2016-11-25' -- Black Friday
                 ,'2016-12-26' -- Christmas Day
                )

Returns

13.370138875

EDIT - If Interested and Just For Fun - Two Functions TVF and SVF

The Table-Valued-Function

CREATE FUNCTION [dbo].[MyFunctionTV] (@D1 datetime,@D2 datetime)
Returns Table 
As
Return (  

Select Value = Count(*)/60.0/24.0
 From  (
         Select Top (DateDiff(MINUTE,@D1,@D2)) 
                D=DateAdd(MINUTE,-1+Row_Number() Over (Order By (Select NULL)),@D1) 
          From  master..spt_values N1,master..spt_values N2
       ) N
 Where Datename(WeekDay,D) Not In ('Saturday','Sunday')
   and D Not In ( '2016-01-01' -- New Year's Day
                 ,'2016-01-18' -- Martin Luther King, Jr,
                 ,'2016-02-15' -- Washington''s Birthday
                 ,'2016-03-25' -- Good Friday
                 ,'2016-05-30' -- Memorial Day
                 ,'2016-07-04' -- Independence Day
                 ,'2016-09-05' -- Labor Day
                 ,'2016-11-24' -- Thanksgiving
                 ,'2016-11-25' -- Black Friday
                 ,'2016-12-26' -- Christmas Day
                )
)
-- Goal   : 13.370138875
-- Select * from [dbo].[MyFunctionTV]('2017-03-13 11:00:02','2017-03-30 19:53:59')

The Scalar-Valued-Function

CREATE FUNCTION [dbo].[MyFunctionSV] (@D1 datetime,@D2 datetime)
Returns float
As
Begin
Return (  

Select Count(*)/60.0/24.0
 From  (
         Select Top (DateDiff(MINUTE,@D1,@D2)) 
                D=DateAdd(MINUTE,-1+Row_Number() Over (Order By (Select NULL)),@D1) 
          From  master..spt_values N1,master..spt_values N2
       ) N
 Where Datename(WeekDay,D) Not In ('Saturday','Sunday')
   and D Not In ( '2016-01-01' -- New Year's Day
                 ,'2016-01-18' -- Martin Luther King, Jr,
                 ,'2016-02-15' -- Washington''s Birthday
                 ,'2016-03-25' -- Good Friday
                 ,'2016-05-30' -- Memorial Day
                 ,'2016-07-04' -- Independence Day
                 ,'2016-09-05' -- Labor Day
                 ,'2016-11-24' -- Thanksgiving
                 ,'2016-11-25' -- Black Friday
                 ,'2016-12-26' -- Christmas Day
                )
)
End
-- Goal   : 13.370138875
-- Select [dbo].[MyFunctionSV]('2017-03-13 11:00:02','2017-03-30 19:53:59')