Code:

declare @DealID bigint=31129, @Formula nvarchar(50) BEGIN declare @premium float, @date_end date, @date_begin date select @date_end=d.DateEnd ,@date_begin = d.DateBegin ,@premium=p.InsPremia from Deals d (nolock) inner join Products p on p.DealID = d.ID where d.ID=@DealID begin set @Formula = cast(@premium as nvarchar) +'/'+cast((Datediff(d,@date_begin, @date_end)+1) as nvarchar) end select @premium premium,@Formula formula END

Query result:

 premium formula 63571070 6.35711e+007/359

Expected Result: 63571070/359
I tried to change the type of @Formula nvarchar(50) на float
But the error:
Error converting data type nvarchar to float
How to convert?

  • 3
    Convert float to decimal and then to varchar. - msi
  • instead of cast (@premium as nvarchar) use STR (@premium) - Ruslan_K
  • In SqlServer 2012 and later, the FORMAT function is available: declare @premium float = 63571070; select format(@premium, 'F0'); declare @premium float = 63571070; select format(@premium, 'F0'); - i-one

1 answer 1

You may have the problem of losing information or its distortion:

 DECLARE @premium FLOAT = 63571070.543453 SELECT @premium, CAST(@premium as nvarchar) + N'/395' , CAST(CAST(@premium as DECIMAL(32,16)) as VARCHAR) + N'/395' , CAST(CAST(@premium as DECIMAL) as VARCHAR) + N'/395'

In this example, you can get the distorted data like: 63571070.5434530004858971/395 or lose the decimal part: 63571071/395
To avoid such problems either do not use FLOAT right away or set the required maximum accuracy as: DECIMAL(10,5)