MSSQL money datatype and <cfqueryparam/>

2011 April 07
tags: ColdFusion · MSSQL
by Paul Marcotte

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.