Using the CAST function

In some cases, a column's data type may not be compatible with the processing you want to do. For example, query columns returned by the cfhttp tag are all of type CF_SQL_VARCHAR, even though the contents may be numeric. In this case, you can use the Query of Queries CAST function to convert a column value into an expression of the correct data type.

The syntax for the CAST function is as follows:

CAST ( expression AS castType )

Where castType is one of the following:

• BINARY
• BIGINIT
• BIT
• DATE
• DECIMAL
• DOUBLE
• INTEGER
• TIME
• TIMESTAMP
• VARCHAR

For example:

<cfhttp url="http://quote.yahoo.com/download/quotes.csv?Symbols=csco,jnpr&format=sc1l1&ext=.csv"
        method="GET"
        name="qStockItems"
        columns="Symbol,Change,LastTradedPrice"
        textqualifier=""""
        delimiter=","
        firstrowasheaders="no">  
<cfoutput>
  <cfdump var="#qStockItems#">
  <cfdump var="#qStockItems.getColumnNames()#">
</cfoutput>

<cfoutput>
  <cfloop index="i" from="1" to="#arrayLen(qStockItems.getColumnNames())#">
       #qStockItems.getMetaData().getColumnTypeName(javaCast("int",i))#<br/>
  </cfloop>
</cfoutput>
<cftry>
    <cfquery name="hello" dbtype="query">
        SELECT SUM(CAST(qStockItems.LastTradedPrice as INTEGER)) 
         AS SUMNOW from qStockItems
    </cfquery>
    <cfcatch>Error in Query of Queries</cfcatch>
</cftry>

<cfoutput>
  <cfdump var="#hello#">
</cfoutput>

View comments in LiveDocs