MSSQL money datatype and <cfqueryparam/>
This is just a quick post about the <cfqueryparam/> and cf_sql_type to use with when working with Microsoft SQL Server (MSSQL) money datatype. While working on a recent project the client specified the db schema and I had to work with a predefined schema. I typically don't use the money datatype, when designing a db schema, preferring instead to use decimal(8,2) for fields where I'm storing monetary data. As we began loading data into a table with money as the datatype, we noticed that amounts like 129.99 were being rounded up to 130. The ColdFusion 9 reference for <cfqueryparam/> does not mention the money datatype, but recommends using cf_sql_money for the double datatype. The ColdFusion 8 reference suggests using the cf_sql_decimal type when storing data to decimal, money, smallmoney mssql datatypes. I followed the suggestion in the CF8 reference, but ran into the rounding error. Finally, I was hipped to the correct cf_sql_type to use by my good friend Matt Quackenbush. He's experienced the same issue in the past and informed me that the solution is to use cf_sql_float.