SQL Server and the CLR have different ways of getting the money

by Michael S. Kaplan, published on 2005/11/07 15:20 -05:00, original URI: http://blogs.msdn.com/b/michkap/archive/2005/11/07/489978.aspx


Yoshihiro Kawabata said (in the suggestion box):

Hello, Michael

I listed up NumberFormatInfo's Culture related property in .NET by C#. The result posted below blog.
http://blogs.sqlpassj.org/yoshihirokawabata/archive/2005/11/06/14767.aspx

And then, the result is very different with SQL Server's Currency Symbols.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_5c69.asp

1.A lot of culture's .NET currency symbol have over 2 charactors but SQL Server always 1 char.
2.SQL Server's Decimal Seprarator and Group Separator always , and . but there are so many valiation , . / - in .NET.
3.SQL Server's positivePattern always is $10, but there are a lot of valiation 10 $, $ 10 etc, in .NET

We need to common currency between applications.

I would take the table put together with your help as much more representative of what SQL Server is doing, myself. :-)

The problem here is a different one than was posted to talk about SQLCLR consistency in collation, entitled String.Compare is for sissies (not for people who want SQLCLR consistency). But in a way it is much more insidious. We are basically looking at two entirely different models:

As you can see, the main difference has to do with SQL Server attempting to be independent of culture/locale settings, while the .NET Framework embraces its dependence on these things.

While I would like to embrace the plan of a common currency handling between Microsoft applications, I am not sure how it could really happen, though. Because neither Windows nor the .NET Framewok are changing their reliace on the locale/culture preferences in parsing/formatting operations.

And if SQL Server made any changes to their model, they would break any customer using them today. Which is also not so great. It would have to be new conversion mechanisms of some sort so that the old (broken) method was still possible.

In the long run, I would love it if blogs like the SQLCLR one and the people behind it could be pushing on the compatibility problems here; they are certainly not trivial.

In the meantime, I will admit my bias for the NLS plan and not use the SQL Server locale-independent mishmosh for handling strings and converting them to various types. :-)

 

This post brought to you by "ยค" (U+00a4, a.k.a. CURRENCY SIGN)


# Yoshihiro kawabata on 10 Nov 2005 4:05 AM:

Hello, Thank you for picking up our suggestion.
I agree to solution by SQLCLR.
And I hope that MSKB or .NET/SQL Help reveal a difference between .NET/SQL Currency,
because so many people dosent konw these issue.

# Michael S. Kaplan on 10 Nov 2005 9:07 AM:

I agree completely -- and hope that such KB/doc/core support can happen in the future....

referenced by

2007/11/09 If you get a yen for something a bit wider you might be out of luck

go to newer or older post, or back to index or month or day