How do I convert cents retained as integers to dollars as decimals?
4
votes
1
answer
2834
views
I have *cent* values, retained as
integers
, that I need to convert to *dollars* and show them as a decimals
.
**Example**
A value is kept as 1925
, so I need to be able to return 19.25
.
This is what I had been doing in MySQL
format(price/ 100, 2) as amount
and when summing the value, I use
cast(SUM(price) / 100 AS DECIMAL(12, 2)) as total_amount
These seem to work fine, but when moving over to Redshift, I am not sure if I am using the right function.
**Current attemtp**
In Redshift, I try with
price :: DECIMAL(12, 2) as amount
and then
SUM(price)/ 100 :: DECIMAL(12, 2) as total_amount
In both instances, I get 4 decimal points in my results? Is using wrapping these functions with trunc(,2)
a good way to solve this?
Asked by D3AD_PIX3L
(41 rep)
Sep 12, 2017, 09:47 PM
Last activity: May 10, 2023, 02:01 PM
Last activity: May 10, 2023, 02:01 PM