0

The database I am working with has ntext datatypes which according to my understanding is evil. I used CAST to varchar to fix the issues in the below trimmed SQL Server query.

Anyway, my question is this: How can I make the following rounding part round correctly? Every time I try to round the column, it gives me an error:

Error converting data type varchar to float.

or

When I do ROUND(CAST(T0.[SLength1] AS numeric),2)

Error converting data type varchar to numeric.

I have tried casting almost every single way and it gives me an error about converting. I have tried rounding without casting and it gives the same error.

    select *
from (
    select distinct
        ,ISNULL(CAST(T0.[ItemCode] as varchar(max)), '') as ItemCode
        ,SWidth1 = case ISNULL(CAST(T0.[SWidth1] as numeric), '0')
            when '0'
                then ''
            else ROUND(ISNULL(CAST(T0.[SWidth1] as varchar(max)), '0'), 2, 0) + '"'
            end
        ,SHeight1 = case ISNULL(CAST(T0.[SHeight1] as numeric), '0')
            when '0'
                then ''
            else ISNULL(CAST(T0.[SHeight1] as varchar(max)), '0') + '"'
            end
    from Warehouse_One_Live.dbo.OITM T0
    inner join Warehouse_One_Live.dbo.ITM1 T1 on T0.[ItemCode] = T1.[ItemCode]
    inner join Warehouse_One_Live.dbo.OITW T2 on T0.[ItemCode] = T2.[ItemCode]
    where T1.[PriceList] = '1'
        and (
            WhsCode like 'DS'
            or WhsCode like '01'
            )
        and (
            U_Condition like 'N'
            or U_Condition like 'B'
            or U_Condition like 'R'
            )
    ) as a
where a.RowNumber = 1;

sp_columns oitm:
ItemCode    -9  nvarchar    20  40  NULL    NULL    0

ItemName    -9  nvarchar    100 200 NULL    NULL    1

FrgnName    -9  nvarchar    100 200 NULL    NULL    1

ItmsGrpCod  5   smallint    5   2   0   10  1

CstGrpCode  5   smallint    5   2   0   10  1

VatGourpSa  -9  nvarchar    8   16  NULL    NULL    1

CodeBars    -9  nvarchar    16  32  NULL    NULL    1

VATLiable   1   char    1   1   NULL    NULL    1

PrchseItem  1   char    1   1   NULL    NULL    1

SellItem    1   char    1   1   NULL    NULL    1

InvntItem   1   char    1   1   NULL    NULL    1

OnHand  2   numeric 19  21  6   10  1

IsCommited  2   numeric 19  21  6   10  1

OnOrder 2   numeric 19  21  6   10  1

IncomeAcct  -9  nvarchar    15  30  NULL    NULL    1

ExmptIncom  -9  nvarchar    15  30  NULL    NULL    1

MaxLevel    2   numeric 19  21  6   10  1

DfltWH  -9  nvarchar    8   16  NULL    NULL    1

CardCode    -9  nvarchar    15  30  NULL    NULL    1

SuppCatNum  -9  nvarchar    17  34  NULL    NULL    1

BuyUnitMsr  -9  nvarchar    100 200 NULL    NULL    1

NumInBuy    2   numeric 19  21  6   10  1

ReorderQty  2   numeric 19  21  6   10  1

MinLevel    2   numeric 19  21  6   10  1

LstEvlPric  2   numeric 19  21  6   10  1

LstEvlDate  11  datetime    23  16  3   NULL    1

CustomPer   2   numeric 19  21  6   10  1

Canceled    1   char    1   1   NULL    NULL    1

MnufctTime  4   int 10  4   0   10  1

WholSlsTax  1   char    1   1   NULL    NULL    1

RetilrTax   1   char    1   1   NULL    NULL    1

SpcialDisc  2   numeric 19  21  6   10  1

DscountCod  5   smallint    5   2   0   10  1

TrackSales  1   char    1   1   NULL    NULL    1

SalUnitMsr  -9  nvarchar    100 200 NULL    NULL    1

NumInSale   2   numeric 19  21  6   10  1

Consig  2   numeric 19  21  6   10  1

QueryGroup  4   int 10  4   0   10  1

Counted 2   numeric 19  21  6   10  1

OpenBlnc    2   numeric 19  21  6   10  1

EvalSystem  1   char    1   1   NULL    NULL    1

UserSign    5   smallint    5   2   0   10  1

FREE    1   char    1   1   NULL    NULL    1

PicturName  -9  nvarchar    200 400 NULL    NULL    1

Transfered  1   char    1   1   NULL    NULL    1

BlncTrnsfr  1   char    1   1   NULL    NULL    1

UserText    -10 ntext   1073741823  2147483646  NULL    NULL    1

SerialNum   -9  nvarchar    17  34  NULL    NULL    1

CommisPcnt  2   numeric 19  21  6   10  1

CommisSum   2   numeric 19  21  6   10  1

CommisGrp   5   smallint    5   2   0   10  1

TreeType    1   char    1   1   NULL    NULL    1

TreeQty 2   numeric 19  21  6   10  1

LastPurPrc  2   numeric 19  21  6   10  1

LastPurCur  -9  nvarchar    3   6   NULL    NULL    1

LastPurDat  11  datetime    23  16  3   NULL    1

ExitCur -9  nvarchar    3   6   NULL    NULL    1


ExitPrice   2   numeric 19  21  6   10  1

ExitWH  -9  nvarchar    8   16  NULL    NULL    1

AssetItem   1   char    1   1   NULL    NULL    1

WasCounted  1   char    1   1   NULL    NULL    1

ManSerNum   1   char    1   1   NULL    NULL    1

SHeight1    2   numeric 19  21  6   10  1

SHght1Unit  5   smallint    5   2   0   10  1

SHeight2    2   numeric 19  21  6   10  1

SHght2Unit  5   smallint    5   2   0   10  1

SWidth1 2   numeric 19  21  6   10  1

SWdth1Unit  5   smallint    5   2   0   10  1

SWidth2 2   numeric 19  21  6   10  1

SWdth2Unit  5   smallint    5   2   0   10  1

SLength1    2   numeric 19  21  6   10  1

SLen1Unit   5   smallint    5   2   0   10  1

Slength2    2   numeric 19  21  6   10  1

SLen2Unit   5   smallint    5   2   0   10  1

SVolume 2   numeric 19  21  6   10  1

SVolUnit    5   smallint    5   2   0   10  1

SWeight1    2   numeric 19  21  6   10  1

SWght1Unit  5   smallint    5   2   0   10  1


SWeight2    2   numeric 19  21  6   10  1

SWght2Unit  5   smallint    5   2   0   10  1

BHeight1    2   numeric 19  21  6   10  1

BHght1Unit  5   smallint    5   2   0   10  1

BHeight2    2   numeric 19  21  6   10  1

BHght2Unit  5   smallint    5   2   0   10  1

BWidth1 2   numeric 19  21  6   10  1

BWdth1Unit  5   smallint    5   2   0   10  1

BWidth2 2   numeric 19  21  6   10  1

BWdth2Unit  5   smallint    5   2   0   10  1

BLength1    2   numeric 19  21  6   10  1

BLen1Unit   5   smallint    5   2   0   10  1

Blength2    2   numeric 19  21  6   10  1

BLen2Unit   5   smallint    5   2   0   10  1

BVolume 2   numeric 19  21  6   10  1

BVolUnit    5   smallint    5   2   0   10  1

BWeight1    2   numeric 19  21  6   10  1

BWght1Unit  5   smallint    5   2   0   10  1

BWeight2    2   numeric 19  21  6   10  1
  • Can you post the relevant column names and data that is in the row you are testing this on? – Tony Hinkle Apr 14 '15 at 17:44
  • There are a LOT of columns in the full query. I have posted some of the relevant sp_columns above – Ahmed Apr 14 '15 at 20:58
  • 1
    Be aware that SQL Server is allowed to try and do this cast even on rows that do not meet the WHERE criteria. Is there any data in the table int hat column that would not cast? – AakashM Apr 15 '15 at 8:27
  • Thank you Aakash, I will inform customer of this and hopefully he can move along since I completed other requirements.. – Ahmed Apr 15 '15 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.