Get the whole part of a decimal number without rounding

ismael · August 2017

Good morning.
I need Get the whole part of a decimal number without rounding.

I have this:

case when cast(f1 as integer)/3600<1 then '0' when cast(f1as integer)/3600>=1 then
cast(f1 as integer)/3600 END

I try this:
case when truncate (cast(f1 as integer)/3600<1,1) then '0'
when truncate ( cast(f1 as integer)/3600>=1,1) then
truncate (cast (f1 as integer)/3600,1) END

But not is ok

Thanks.

marcothesane · August 2017

Can you please add some example data for f1?
The CASE expression, to me, is not part of your question.
In Vertica, :: is the CAST operator. And casting leads to rounding. To get the integral part of a numeric with decimal fractions, I would use the FLOOR() function.
Maybe the micro demo below sheds some light on what you're searching for.
Whatever you want to do with your CASE expression afterwards is up to you:
WITH
numerics(num) AS (
SELECT 0.125
UNION ALL SELECT 0.25
UNION ALL SELECT 0.375
UNION ALL SELECT 0.5
UNION ALL SELECT 0.625
UNION ALL SELECT 0.75
UNION ALL SELECT 0.875
UNION ALL SELECT 1
)
SELECT
num
, num::INT
, FLOOR(num)::INT
, CEIL(num)::INT
FROM numerics;

num |num|FLOOR|CEIL
0.125| 0| 0| 1
0.250| 0| 0| 1
0.375| 0| 0| 1
0.500| 1| 0| 1
0.625| 1| 0| 1
0.750| 1| 0| 1
0.875| 1| 0| 1
1.000| 1| 1| 1

sKwa · August 2017

Hi!

dbadmin=> select f1, f1 // 3600 from ismael;
  f1   | ?column? 
-------+----------
  3000 |        0
  3200 |        0
  3400 |        0
  3600 |        1
  3800 |        1
  3900 |        1
  3999 |        1
 13320 |        3
(11 rows)

Good Luck.

ismael · August 2017

Thanks so much Solved

Get the whole part of a decimal number without rounding

Comments

Leave a Comment