Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Get the whole part of a decimal number without rounding

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.

Comments

  • marcothesanemarcothesane Administrator

    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

  • edited 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.

  • Thanks so much Solved

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.