Search code examples
sqlsql-servercastingsqldatatypesconverters

SQL (Server) error converting varchar to float. Text + amount

This code works and outputs nicely

Code:

select (sum(SchemeAccount.AdoptionAmount)- convert(varchar,Scheme.Acquisition,103)) [£ Output]

Output:

enter image description here

I now want the amount to be preceded by the word 'Variance'

So I try:

 select 'variance'+  (sum(SchemeAccount.AdoptionAmount)- convert(varchar,Scheme.Acquisition,103)) [£ Output]

But then get:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

What I want is 'Variance £73,102,500' or at least 'Variance 73102500'

I have tried a lot of CAST and convert(varchar,col,103) functions and have either not been successful or got 'Variance 73e105' or something like that.

If more info is needed on my current data types, I can try to acertain.

Solution

  • Use concat(). I assume you intend:

    select concat('variance',
              convert(decimal(20, 4), sum(SchemeAccount.AdoptionAmount)),
              '-',
              convert(varchar(255), Scheme.Acquisition, 103)
             ) as [£ Output]