Which cf_sql data type should I choose for the MS-SQL SmallMoney field?

Data and Databases
1

I’m trying to decide on the best cf_sql type to work with an MS-SQL column defined as SmallMoney. The documentation does not clearly indicate whether to use CF_SQL_MONEY or CF_SQL_MONEY4, and I’m unsure which option will offer more reliable results when handling small monetary values. I encountered this issue while querying my payments table. Below is an example illustrating a similar scenario:

<cfquery name="retrievePaymentData" datasource="FinanceDSN">
  SELECT PaymentAmount FROM FinancialRecords
  WHERE PaymentAmount = <cfqueryparam value="#paymentVal#" cfsqltype="CF_SQL_CURRENCY_TYPE" />
</cfquery>

Any advice on the optimal choice would be greatly appreciated.

2

i found using cf_sql_money to be the best bet, it proved more reliable during my experiemces. cf_sql_money4 sometimes gave odd rouding issues. so, sticking with cf_sql_money keeps things straight.

3

In practice, the CF_SQL_MONEY type has proven most effective when handling MS-SQL SmallMoney fields. This approach provides reliable numeric precision and reduces the risk of rounding issues observed with other types. My experience shows that using CF_SQL_MONEY ensures that calculations and comparisons work as expected during transactions. While CF_SQL_MONEY4 may appear as an alternative, it does not offer the same consistency and may lead to unexpected behavior when dealing with financial data.

4

hey im leaning towards using cf_sql_money based on my tests, it works pretty good. any of you tried cf_sql_money4? what sort of rounding quirks have you run into in your experiemces?