为何INT类型不能作为FORMAT函数的第二个参数？SQL问题咨询

报错原因 #

你写的FORMAT(AVG(a.DaysB4Billing), 2)触发Msg 8116错误，核心原因是FORMAT函数的第二个参数必须是格式字符串，不能直接传入整数2。SQL Server要求该参数为nvarchar类型，传入int类型自然会提示参数类型无效。

平均值取整问题原因 #

用FORMAT(AVG(a.DaysB4Billing), 'n2')得到的是2而非预期的2.73，是因为DaysB4Billing是int类型——SQL Server中对int类型求AVG时，会执行整数除法，直接舍弃小数部分得到整数结果，再格式化也只能得到2.00，而非精确的2.73。

解决方法 #

要得到带小数的精确平均值，需要先把DaysB4Billing转换为浮点或decimal类型，再计算平均值，最后用FORMAT格式化：

• 将AVG(a.DaysB4Billing)改为AVG(CAST(a.DaysB4Billing AS DECIMAL(10,2)))，确保平均值计算保留小数
• 保留FORMAT(..., 'n2')的格式字符串写法，即可得到带两位小数的精确结果

修正后的查询语句 #

SELECT
    a.BillMonthYear
    ,FORMAT(AVG(CAST(a.DaysB4Billing AS DECIMAL(10,2))), 'n2') AS AvgDaysB4Billing
    ,COUNT(a.DaysB4Billing) AS 'Number of invoices'
FROM
    (SELECT  
         [CUST_ID]
         ,[PREMISE_ID]
         ,CONVERT(date, [INV_TRANSACTION_DATE]) AS 'INV_TXN_DATE'
         ,CONVERT(date,[USG_TRANSACTION_DATE]) AS 'USG_TXN_DATE'
         ,CONVERT(date,[BILL_DATE]) AS 'BILL_DATE'
         ,DATENAME(weekday, bill_date) AS 'BILLWK_DATE'
         ,CONCAT(DATENAME(MONTH, bill_date), ' ', DATENAME(YEAR, bill_date)) AS 'BillMonthYear'
         ,CASE
             WHEN [INV_TRANSACTION_DATE] = [USG_TRANSACTION_DATE] 
                 THEN CAST([BILL_DATE] - [INV_TRANSACTION_DATE] AS INT)
             WHEN [INV_TRANSACTION_DATE] > [USG_TRANSACTION_DATE] 
                 THEN CAST([BILL_DATE] - [INV_TRANSACTION_DATE] AS INT)
             WHEN [INV_TRANSACTION_DATE] < [USG_TRANSACTION_DATE] 
                 THEN CAST([BILL_DATE] - [USG_TRANSACTION_DATE] AS INT)
          END AS 'DaysB4Billing'
         ,[INV_SENDER_NAME]
         ,[INV_TYPE]      
         ,CONVERT(DATE,[SERVICE_START]) AS 'SVC_START'
         ,CONVERT(DATE,[SERVICE_END]) AS 'SVC_END'
         ,[CUST_STATUS]    
         ,[BILL_NO]
         ,[EXCEPTION_STAT1]
         ,[EXCEPTION_STAT2]
         ,[EXCEPTION_STAT3]
         ,[EXCEPTION_STAT4]
         ,[EXCEPTION_STAT5]
         ,[EXCEPTION_STAT6]
         ,[EXCEPTION_STAT7]
         ,[EXCEPTION_STAT8]
         ,[EXCEPTION_STAT9]
         ,[EXCEPTION_STAT10]
         ,[EXCEPTION_DATE]
         ,[INV_STATUS]
         ,[USG_STATUS]
         ,[BILL_STATUS]
         ,[NOTES]
         ,[UPDATE_BY]
         ,[purpose_code]
         ,[original_invoice_number]
         ,[PRIOR_BILL_STATUS]
         ,[EXCEPTION_ALL]
     FROM 
         [B1].[B1].[dbo].[INV_USG_XREF]
     WHERE 
         purpose_code = '00'
         AND bill_date BETWEEN '2021-06-01' AND '2021-06-30') a
GROUP BY 
    a.BillMonthYear

额外建议：把日期条件写成'2021-06-01'这种ISO标准格式，避免因服务器日期格式设置导致的解析错误。

内容的提问来源于stack exchange，提问作者Gustavo Rodriguez