Re: Smallmoney and money rounded to 2 decimals?

On 04/12/2018 09:41 AM, Frediano Ziglio wrote:
> 2018-04-06 12:59 GMT+01:00 Sebastien FLAESCH <[email protected]>:
>> Hi all,
>>
>> Using FreeTDS 1.00.9:
>>
>> When fetching a smallmoney or money data, SQLDescribeCol() returns
>> the sqltype SQL_DECIMAL, precision 10 for smallmoney and or 19 for
>> money and a scale of 4 for both types, which is fine, since smallmoney
>> and money can store 4 decimal digits.
>>
>> Using an SQL_C_CHAR buffer to hold the values, we see that the decimal
>> part is rounded to 2 digits...
>>
>> Is this expected?
>>
>> using other ODBC drivers for SQL Server we get 4 decimal digits.
>>
>> Is there some configuration settings to control this?
>>
>> Thanks!
>> Seb
> 
> Just checked. Yes, apparently Microsoft ODBC drivers format money with
> 4 decimal digit.
> I think in the past tools used 2 digits but they moved to 4 along the way.
> There's actually no way to change that behavior but probably there should be.
> The 2 digits code is currently quite hard coded in src/tds/convert.c
> and src/tds/numeric.c.
> I suppose a good way would be to add a flag to TDSCONTEXT (which is
> used during conversion to pass locale information).
> 
> Frediano
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> https://lists.ibiblio.org/mailman/listinfo/freetds
> 

Hello Frediano, and thanks for considering this!

I am not familiar with FreeTDS code internals and TDSCONTEXT.

I you want to keep backward compatibility, I assume that there will be a new
ODBC connection attribute to control that.

http://www.freetds.org/userguide/odbcconnattr.htm

And if we consider the current behavior as a bug, the default should be 4 decimal
digits, and people expecting 2 will have to define the parameter.

MoneyFormatDecDigits=2

You may also consider rounding issues:
Currently FreeTDS rounds to 2 digits...
What happens with a db value like 0.1160 ?
To be fetched as 0.12, or as 0.11 ?
When 0.11, is this data loss or ok?
It's monetary data!

I vote for a bug => return always 4 digits.

Seb