SQL: Check column number format / Change language or format required

Question

The problem

I'm from South America, and when I was trying to install SQL Server in my PC I was required to install the Spanish version (Spain format) and for that, I also had to change the configuration of my computer.

What is wrong with the spanish (from Spain) format?:

Well, they use as indicator of decimals a comma ',' instead of a dot. So 4.5 is 4,5 under this format.

How this affects the project? (in ASP.NET Core):

Most of the devices in my country use the 'regular format' (4.5) and just now I encountered a problem where I can't save into the database a number with this format: 4.5

So, I have some questions:

How can I check what kind of format the Database is requesting? (Just to be sure this is the problem)
How can I change the language configuration of my SQL server / or just the format for numbers expected for the database so it starts accepting this format: 4.5?

Here is the definition of the table I'm having problems with:

CREATE TABLE [dbo].[Device] (
[DeviceID]          INT            IDENTITY (1, 1) NOT NULL,
[DeviceName]        NVARCHAR (MAX) NULL,
[Id]                INT            NOT NULL,
[InitialAmount]     INT            DEFAULT ((0)) NOT NULL,
[DeviceStatus]      INT            DEFAULT ((0)) NOT NULL,
[DeviceDateCreated] DATETIME2 (7)  DEFAULT ('0001-01-01T00:00:00.000') NOT NULL,
[StoreID]           INT            DEFAULT ((0)) NOT NULL,
[MachineOwnerID]    INT            DEFAULT ((0)) NOT NULL,
[CoinValue]         REAL           NOT NULL,
[FechaPUnit]        DATETIME2 (7)  DEFAULT ('0001-01-01T00:00:00.000') NOT NULL,
[PUnit]             REAL           NOT NULL,
[SellType]          NVARCHAR (MAX) NULL,
CONSTRAINT [PK_Device] PRIMARY KEY CLUSTERED ([DeviceID] ASC),
CONSTRAINT [FK_Device_Machine_Id] FOREIGN KEY ([Id]) REFERENCES [dbo].[Machine] ([Id]) ON DELETE CASCADE);

Both are defined as real numbers.

This problem was detected on this question: Input(float) gets converted to integer while saving

If you keep your data in suitably typed variables (e.g. `float` or `decimal`) throughout then the format that is used when those are converted to *strings* shouldn't matter. The issue is almost certainly that you're allowing this data to be converted into strings at one or more points and then having issues when the strings get (implicitly) converted back into the appropriate data types. — Damien_The_Unbeliever, Nov 28 '17 at 07:52
What kind of sql-server do you use? What version? You can set containment type to partial on Database level and then change the default language on DB level. Use this for changing it by SQL command https://stackoverflow.com/questions/15705983/how-to-change-default-language-for-sql-server — SqlKindaGuy, Nov 28 '17 at 07:53
@Damien_The_Unbeliever I would have to check but I believe there is no implicit convertion for these variables. I have posted the information of the project in a previous quesiton: https://stackoverflow.com/questions/47524571/inputfloat-gets-converted-to-integer-while-saving These are defined as floats in the project and filled used a form and saved directly using the POST Action. Could you give me some insight? — Luis Alberto Delgado de la Flo, Nov 28 '17 at 07:59
@plaidDK MSSQL 2016. I'm afraid I don't know enough about SQL to do this via SQL command — Luis Alberto Delgado de la Flo, Nov 28 '17 at 08:01

score 0 · Answer 1 · answered Nov 28 '17 at 08:10

You can change the default DB language by using sp_configure - You might need to restart SQL Service to take affect.

You can also read more here: Configre default language

SQL

USE [DBNAME] ;  
GO  
EXEC sp_configure 'default language', 0 ;  
GO  
RECONFIGURE ;  
GO

List of language

╔════════╦═════════════════════╗
║ LANGID ║        ALIAS        ║
╠════════╬═════════════════════╣
║      0 ║ English             ║
║      1 ║ German              ║
║      2 ║ French              ║
║      3 ║ Japanese            ║
║      4 ║ Danish              ║
║      5 ║ Spanish             ║
║      6 ║ Italian             ║
║      7 ║ Dutch               ║
║      8 ║ Norwegian           ║
║      9 ║ Portuguese          ║
║     10 ║ Finnish             ║
║     11 ║ Swedish             ║
║     12 ║ Czech               ║
║     13 ║ Hungarian           ║
║     14 ║ Polish              ║
║     15 ║ Romanian            ║
║     16 ║ Croatian            ║
║     17 ║ Slovak              ║
║     18 ║ Slovenian           ║
║     19 ║ Greek               ║
║     20 ║ Bulgarian           ║
║     21 ║ Russian             ║
║     22 ║ Turkish             ║
║     23 ║ British English     ║
║     24 ║ Estonian            ║
║     25 ║ Latvian             ║
║     26 ║ Lithuanian          ║
║     27 ║ Brazilian           ║
║     28 ║ Traditional Chinese ║
║     29 ║ Korean              ║
║     30 ║ Simplified Chinese  ║
║     31 ║ Arabic              ║
║     32 ║ Thai                ║
║     33 ║ Bokmål              ║
╚════════╩═════════════════════╝

score 0 · Answer 2 · answered Nov 28 '17 at 08:12

In order to change the language, there is a nice Set Language guide at Microsoft docs.

The languages are stored in sys.syslanguages and the languages taken and are set from there.

If you need to permanently change the language then

Go To Security

Logins
Right Click on your username and select Properties
Change the default language to English

This change will persist even after you restart your SSMS

SQL: Check column number format / Change language or format required

2 Answers2