Decimal Separator / Language Setting

Question

Decimal Separator / Language Setting

Alexander G.

SSCrazy

Points: 2705
More actions
August 27, 2008 at 12:58 am

#382413

Hi,
In German the decimal separator usually is a comma ",". OK, I am already used to using the dot when writing SQL-Querys. Until yesterday I also used to believe that any numeric output in SQL Server follows that rule. But now I discovered a couple of strange things (all using SSMS, 2005 with German language setting):
select cast(1.0 / 3.0 as real) -> "0,3333333"
select cast(1.0 / 3.0 as decimal(4,3)) -> "0.333"
After: create view test as select cast(1.0 / 3.0 as decimal(4,3))
select * from test -> "0.333"
Open the view via the context menu ("Open View") -> "0,333"
Can you confirm that behaviour or do you know any properties to achieve a more consistent behaviour?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Quatrei.X SSCrazy Points: 2697 More actions · Answer 1

:)Hi there,

I'm not sure if your asking a question and if you are, correct me if I'm wrong but do you want a decimal value like 0,333 but are having problems using it? If so, Hope this helps...

Just use the decimal data type like you always do and then when you use the sproc that returns the values, try doing something like this

DECLARE @d as DECIMAL(5,3)

SET @d=0.333

SELECT REPLACE(@d, '.', ',')

now, if it will be used in computations, just return it with the original value like so

DECLARE @d as DECIMAL(5,3)

SET @d=0.333

SELECT @d,REPLACE(@d, '.', ',')

I didn't know some people use commas as decimal symbols.

and hide the original value.

^__^ Please tell me if this was helpful. Thanks

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

Alexander G. SSCrazy Points: 2705 More actions · Answer 2

Hi,

sorry for my problem not being too clear:

Actually I have a usage problem with SSMS: I often have to do ad-hoc queries and use SSMS to copy the data to excel. Therefore I need to have a consistent handling of numeric data either using "," or ".", but not sometimes "," and sometimes "." depending on which numeric data type I have (real or decimal) and which UI I am using (select statement or open view).

(Of course I can convert the corresponding string but that is more like a workaround)

Is this a wrong configuration of my server or client or is it a bug that you can confirm?

Thanks

Alexander

jan.nooitgedagt Old Hand Points: 330 More actions · Answer 3

No, you are very clear !

I wander why you dont have an answer until now.

Your problem is only when you convert to 'real'. Not when you convert to decimal

I have a question near the one of you:

How can i configure sql-server so he is storing 'comma' in stead off point for decimals ?