Forum Discussion
Solved
Question about decimal numbers in SQL Server Management Studio
Hello, everyone, I am new using SSMS, I created a data base.
I used the "import flat file option" to import a csv file, in preview data I uncheck the use rich data type detection, in the modify colum section I see that temp and atemp are float, hum is nvarchar(50)
I can see the decimal numbers in a text editor and preview data in SSMS.
The file has some colums have decimal numbers, this is part of the table in SSMS preview data:
After I import the file, I run select * from bike_share_yr_0 , the temp and atem are not decimal numbers
I tried using ChatGPT to see if there are something I can change in the configuartions of SSMS, but nothing worked.
Other option is doing some calculations like:
UPDATE bike_share_yr_0
SET atemp = atemp / 10000;
This work fine for few colums, but what happend if a have a lot of files an every colum have decimales like atemp.
Is possible to fix and see in SSMS the decimal numbers like in preview data? What can I do to fix that? Thank you for helping
en-US has the period as the decimal separator, so this is just wrong.
You're on the right track though.
Locally, using SSMS 20 and SQL Server 2022, on a OS with Norwegian regional formatting and "Latin1_General_CI_AS" as SQL Server collation, I am denied converting numbers on the format "0.4" as floats:TITLE: Microsoft SQL Server Management Studio
------------------------------Error inserting data into table. (Microsoft.SqlServer.Import.Wizard)
------------------------------
ADDITIONAL INFORMATION:Error inserting data into table. (Microsoft.SqlServer.Prose.Import)
------------------------------
The given value '0.4' of type String from the data source cannot be converted to type float for Column 9 [Basic]. (Microsoft.Data.SqlClient)
------------------------------
Failed to convert parameter value from a String to a Double. (Microsoft.Data.SqlClient)
------------------------------
Input string was not in a correct format. (mscorlib)
------------------------------
BUTTONS:OK
------------------------------Might be earlier versions assumed it was the regional thousands separator.
In Norwegian regional formatting, the comma is the decimal separator, and I was required to update my source file to follow my regional formatting scheme.
Alternatively, you could temporarily change the regional format of the machine you're running SSMS on, restart SSMS, then try again.That worked for me (note: I have a local install of SQL Server. It was not restarted after the regional format change.)
marlon12_note that the preview is before you change your column data types. The preview just tells you how your data is columnated, not how it will be stored as data.
and atem are not decimal numbers
They are decimals, but by default strings are converted to decimal using en-US format and this means the dot is not treaten a decimal separator but as thousend separator and so you get thousend's values. You have to modify the string-2-decimal/regional format.
en-US has the period as the decimal separator, so this is just wrong.
You're on the right track though.
Locally, using SSMS 20 and SQL Server 2022, on a OS with Norwegian regional formatting and "Latin1_General_CI_AS" as SQL Server collation, I am denied converting numbers on the format "0.4" as floats:TITLE: Microsoft SQL Server Management Studio
------------------------------Error inserting data into table. (Microsoft.SqlServer.Import.Wizard)
------------------------------
ADDITIONAL INFORMATION:Error inserting data into table. (Microsoft.SqlServer.Prose.Import)
------------------------------
The given value '0.4' of type String from the data source cannot be converted to type float for Column 9 [Basic]. (Microsoft.Data.SqlClient)
------------------------------
Failed to convert parameter value from a String to a Double. (Microsoft.Data.SqlClient)
------------------------------
Input string was not in a correct format. (mscorlib)
------------------------------
BUTTONS:OK
------------------------------Might be earlier versions assumed it was the regional thousands separator.
In Norwegian regional formatting, the comma is the decimal separator, and I was required to update my source file to follow my regional formatting scheme.
Alternatively, you could temporarily change the regional format of the machine you're running SSMS on, restart SSMS, then try again.That worked for me (note: I have a local install of SQL Server. It was not restarted after the regional format change.)
marlon12_note that the preview is before you change your column data types. The preview just tells you how your data is columnated, not how it will be stored as data.Hi, sorry for late reponse, thank you for you comment, I changed to regional format and it worked like I except, thank you so much
