1

Scenario:

I have a Sql Server instance on English Windows Server.

But I also have my own ITALIAN client with Sql Server Management Studio and SSMS regional settings set to Italian.

So when I extract data, i need separator as follow :

, (comma) for decimal

. (point) for thousands

Example: 1.065,00


Instead , Sql Server Management Studio return me number like:

1065.0000

(point for decimal separator)

If I copy & paste into Excel all data are mess..


Example: point , but i need comma!

example numbers with points instead of commas


How can I do without writing specific query with "Format" or similar?

Thanks

Improve this question
2
  • Hi @stighy, I use Italian regional setting as well and I never faced such problem. Can you paste a picture of the data in SSMS and exported to Excel? Thank you
    – Francesco Mantovani
    Oct 28, 2020 at 21:54
  • Added a screenshot thanks Francesco
    – stighy
    Oct 29, 2020 at 8:59

2 Answers 2

Reset to default
0

SSMS is inconsistent. For some data types, it uses comma (I'm Swedish) as decimal separator. For other types, it uses dot.

For money and float SSMS presents comma, but for decimal I get dot. None of them gives me a thousand separator.

SELECT CAST(3000.1 AS money), CAST(3000.1 AS float), CAST(3000.1 AS decimal(9,2))

I guess that one could consider the inconsistency a bug.

SSMS was never designed to be an end-user tool, so we pretty much have to accept what we are given. If we don't like how SSMS present the data returned from SQL Server we can open a feature request for this to Microsoft: https://feedback.azure.com/forums/908035-sql-server

I wouldn't have too much hope for this to be addressed.

Improve this answer
3
  • Tibor, does ADS have the same (or similar) incorrect handling of regionalization? Or would using ADS be a possible work around?
    – AMtwo
    Oct 28, 2020 at 16:10
  • Tibor, what do you suggest as alternative to SSMS
    – stighy
    Oct 28, 2020 at 16:43
  • ADS at least uses comma as decimal separators for all the three types in my example. I do remember it used dot for decimal at some point, so they seem to have fixed that. I don't have the need you do, but I would probably define my needs (if you have further) and request them for ADS.
    – Tibor Karaszi
    Oct 28, 2020 at 18:46
0

I ran into that issue too today.

Same database/different clients, both using SSMS

  1. as Windows Server in English
  2. Client-PC in German

The same statement on the same database gives following results:

  1. 1,000,000.11
  2. 1.000.000,11

I found out that it's related to the culture settings (regional format) of Windows. I changed it on the client PC to English-US and then I got the results of 1) too.

Hope to help everybody outside with that

Improve this answer
1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Marcello Miorelli
    Nov 11, 2022 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.