0

I am inserting some data from python to sql server. In the dataframe, the data looks ok. However after inserting into sql table, the number converts to some exponential expression. I tried to convert it into a number but the output is not correct. Below is the screenshot of the data in the table:

enter image description here

The first row number should be: 9013513. However, when I convert the exponential number to real number using the code below, it comes out as 9013510. Below is my code:

declare @stproduct nvarchar(255) 
set @stproduct = '9.01351e+006'
SELECT CONVERT(numeric(16,0), CAST(@stproduct AS FLOAT))
Sriram
  • 433
  • 4
  • 20
  • I think the SQL Server is returning a valid result. The Exponential Conversion in an online tool gives the same 9013510 value. Try casting it in DF inorder to avoid the conversion in SQL. – Srinivasan Rajasekaran Dec 23 '22 at 07:40
  • i want the real number instead of the exponential number – Sriram Dec 23 '22 at 07:41
  • Don't use float data type - it's not a precise data type – Dale K Dec 23 '22 at 07:46
  • @DaleK i tried using Real instead of float. Same result – Sriram Dec 23 '22 at 07:49
  • Real **IS** still a float, you want decimal – Dale K Dec 23 '22 at 07:51
  • 2
    It looks like it is getting converted to a string containing the number in scientific notation before it even gets to SQL Server. So SQL Server would have no way of knowing that the value is supposed to be 901351**3** anyway as that is already lost as the string doesn't even contain the trailing 3 – Martin Smith Dec 23 '22 at 08:35
  • 1
    If the value was `9013513` then the `float` value would be `9.013513e+006`. YOu need to find out why your value in your database is wrong, not why the "conversion is wrong", because the conversion *isn't* wrong. – Thom A Dec 23 '22 at 09:13
  • 1
    consider converting the float to decimal in python, and passing a decimal parameter in the query. – Gert-Jan Dec 24 '22 at 11:39
  • @Gert-Jan how do i convert from float to decimal in pandas python? There is no datatype for decimal – Sriram Dec 25 '22 at 15:04
  • I am not familiar with pandas python, but I'm sure you'll be able to find it. [Example](https://stackoverflow.com/questions/38094820/how-to-create-pandas-series-with-decimal) – Gert-Jan Dec 30 '22 at 09:43

0 Answers0