你的函数当前返回一个表。我不确定这是否是必需的，但您可能需要一个函数，它只返回表示HMS中持续时间的字符串，或者表示HMS中持续时间的Time值。

创建另一个函数。

  CREATE FUNCTION dbo.UDF_SecondsToHMSVarChar（@Seconds int）RETURNS varchar（20）AS 
 BEGIN 
 RETURN（
 SELECT CONVERT（VARCHAR（20），@Seconds / 3600）+'：'
 + RIGHT（'00'+ CONVERT（VARCHAR（2），（ （'00'+ CONVERT（VARCHAR（2），@秒％60），2）AS [HMS] 
）+'：'
 + 
 END 
 

这样会给你回文字，所以你将无法任何计算直接在它上面，如果你只是需要它在报告上显示'原样'，这将是很好的。

要使用该函数，只需要替换当前返回秒和函数和原始的expr ession作为参数传递，例如

 ，（SUM（TimeToAnswerTotal）/ 600）[ASA] 
 

会变成

 ，dbo。 UDF_SecondsToHMSVarChar（（SUM（TimeToAnswerTotal）/ 600））[ASA] 
 

只需将两个日期时间值转换为日期类型即可。基本上这只给你日期时间的日期部分，所以你可以直接与传入日期进行比较。

  WHERE DateOfCalls&gt ; = @Report_Date 
 

变成

  WHERE CAST（DateOfCalls AS DATE）= CAST（@Report_Date AS DATE）
 

I'm writing code on SQL Server 2014 to pull records for a single day. I will then be using Report Builder to create the report.

I'm not sure if my code is right. The times of the shifts are in 'yyyy-mm-dd hh:mm:sss' format starting at 7:45am to 6:15pm. I need to count total calls (and other fields) for that time period per day. I will also need to give a parameter so the viewer can select the date range for the report.

I created a function to convert total seconds to HH:MM:SS format. The code for the function is:

CREATE FUNCTION dbo.UDF_SecondsToHMS(@Seconds int) RETURNS table AS
RETURN (
         SELECT CONVERT(VARCHAR(20), @Seconds / 3600) + ':' 
       + RIGHT('00' + CONVERT(VARCHAR(2), (@Seconds % 3600) / 60) ,2) + ':' 
       + RIGHT('00' + CONVERT(VARCHAR(2), @Seconds%60), 2) AS [HMS] );

The code I have tried is:

SELECT 
     SUM(CallsAnswered) CallsAnswered
    ,SUM(TotalCalls) TotalCalls
    ,(SUM(TimeToAnswerTotal) / 600) [ASA]   --> How do I call the function here too?
----------------------- (I called the function here)
    ,(SELECT 
        (SELECT HMS 
         FROM [UDF_SecondsToHMS](SUM (DATEDIFF(second, AgentLoginTime, AgentLogoutTime)))) [HMS]
      FROM [AgentStats]
      WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, StartDate)) = CAST('2017-03-08' AS DATETIME)
      ) [Actual Shift Hrs.]
----------------------- (End of the function here. I had help calling this function here.)
    ,(SUM(ACDDuration) / 600) [Avg. ACD Time]  --> How do I call the function here too?

FROM [CallsByPeriodStats]

WHERE DateOfCalls >= @Report_Date   --typically the date shows as '2017-02-22 00:00:000'
  AND DateOfCalls <  @Report_Date + 1 day of the month  --typically it should be '2017-02-23 00:00:000'
                              --but what if @Report_Date is last day of month?
GROUP BY CAST[StartDate as DATE]

1. How do I call the function I created to convert seconds to hh:mm:ss in the 2 places I indicated above?
2. If I give a date range, how will it take it if the date is the last date of the month?

Your function currently returns a table. I'm not sure if that is required but you will probably need a function that just returns either a string representing the duration in HMS or a Time value representing the duration in HMS

Create another function something like this..

CREATE FUNCTION dbo.UDF_SecondsToHMSVarChar(@Seconds int) RETURNS varchar(20) AS
BEGIN
    RETURN (
             SELECT CONVERT(VARCHAR(20), @Seconds / 3600) + ':' 
           + RIGHT('00' + CONVERT(VARCHAR(2), (@Seconds % 3600) / 60) ,2) + ':' 
           + RIGHT('00' + CONVERT(VARCHAR(2), @Seconds%60), 2) AS [HMS] 
           )
END

This will just give you text back, so you won't be able to any calculation directly on it, this will be fine if you just need it showing 'as-is' on a report.

To use the function just replace anything that currently is returning seconds with the function and the original expression passed as the parameter e.g.

,(SUM(TimeToAnswerTotal) / 600) [ASA]

would become

, dbo.UDF_SecondsToHMSVarChar((SUM(TimeToAnswerTotal) / 600)) [ASA]

To select only data from date, simply cast the two datetime values to date types when you compare them. Basically this give you only the date portion of the datetime so you can compare it directly with the passed in date.

WHERE DateOfCalls >= @Report_Date

becomes

WHERE CAST(DateOfCalls AS DATE) = CAST(@Report_Date AS DATE)