Why is int an invalid arguement for FORMAT's second parameter?-CodePudding

I am currently trying to obtain the actual result from an average function, but when I use the format function, it is giving me the following error:

Msg 8116, Level 16, State 1, Line 1 Argument data type int is invalid for argument 2 of format function

This is my query:

select
    a.BillMonthYear
    ,format (avg (a.DaysB4Billing), 'n2')
    ,format (avg (a.DaysB4Billing), 2)
    ,count (a.DaysB4Billing) as 'Number of invoices'
    from
(SELECT  
       [CUST_ID]
      ,[PREMISE_ID]
      --,[INV_TRANSACTION_ID]
      ,convert (date,[INV_TRANSACTION_DATE]) as 'INV_TXN_DATE'
      --,[USG_TRANSACTION_ID]
      ,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 '06-01-2021' and '06-30-2021') a
GROUP BY a.BillMonthYear

The result should be 2.73, but it is giving me error message I described above. This is when I add the "format (avg (a.DaysB4Billing), 2)" line, and the one above it does pull the data, but it rounds it down to 2 for some reason.

CodePudding user response：

It's expecting a format string, not an integer value in that position. Try

format (avg (a.DaysB4Billing), 'N')

Reference: Standard numeric format strings

CodePudding user response：

Your line 4 is passing an integer to the format function, which is not supported, hence the error:

select
a.BillMonthYear
,format (avg (a.DaysB4Billing), 'n2')
,format (avg (a.DaysB4Billing), 2) -- right here

Perhaps you meant to pass something 'n2' or something of the sort?

Seems then that what you need is this:

SELECT CAST(ROUND(avg (a.DaysB4Billing), 2,1) as decimal(10,2))

ROUND(2,1) will actually truncate to the 2nd decimal place since a number different than 0 (1) is specified in the second parameter.