I have my date which looks something like this.

I want to get sum of ITEM_QTY which has varchar datatype currently and tried following ways after typecasting but none of them worked.

1. SELECT SUM(TRY_CAST(ITEM_QTY as bigint))
   FROM sales219
   

   Result:

   

2. SELECT SUM(CAST(ITEM_QTY as bigint))
   FROM sales219
   

   Output:

   Error converting data type varchar to bigint.

3. SELECT SUM(convert(bigint,ITEM_QTY))
   FROM sales219
   

   Output:

   Error converting data type varchar to bigint.

4. SELECT SUM(try_convert(bigint,ITEM_QTY))
   FROM sales219
   

   Output:

   

Any suggestion is appreciated.

Thanks!

CodePudding user response：

You will want to remove the double quotes from your data. In the mean time try this:

Select sum(try_convert(bigint,replace([ITEM_QTY],'"',''))) from YourTable

Or rather, fix your importing process to stop putting the quotes there to begin with.