To remove the decimal values from a number (for example 123.14 displayed as 123), use the SQL CAST function. This will convert the amount to the desired data type. In this case an integer value. The syntax looks like this:
SELECT CAST(123.14 AS INT)
or
SELECT CAST(CR073_AMT_01 AS INT) AS Amount01 FROM CR073_ISSUES WHERE ...
Comments
2 comments
Should this also work for EZWriter SQL? We have a custom field that we assigned as Numeric for a phone number (has no dashes in it), but it turns out its inserting decimals in the report. Instead of having to reassign that custom field to a text field, I'd rather truncate the decimals in sql, but it keeps throwing a "Arithmetic overflow error converting expression to data type int."
0 upvotes
I tried this using BIGINT instead.
For example: CAST(ER110_EVT_REGIS.ER110_AMT_01 AS BIGINT) AMT01
Then within Merge Data Formats I inserted this AMT01 field in my report. No errors, but no data is appearing in that column on the report. Any idea?
0 upvotes
Please sign in to leave a comment.