Trying to ROUND UP

Analytics

Trying to ROUND UP

I am having a problem with Rounding.  I am subtracting two dates, then dividing by average months in a year.  But Teradata is giving me an integer and is rounding down.  For example, if I’m subtracting 9/9/15 from 2/1/16, I get 145 days, which divided by Avg. Days in Month (365/12 = 30.41667) = 4.767123.  The number I want is 5, but the number my formula is giving is 4.  How do I round “UP”?  I only want whole numbers.

Here is the line from my code:

 ,case when (bl.change_dt-LAST_ACT_DT)/(365/12) >12 then '>12' else (bl.change_dt-LAST_ACT_DT)/(365/12) end AS tenure   

Can you point me in the right direction?  Thanks.

Tags (2)
2 REPLIES
Junior Contributor

Re: Trying to ROUND UP

All your calculations use INTEGER-arithmethic, truncating the result.

You're not dividing by (365/12 = 30.41667), but by 30

Cast one of the operands to a FLOAT/NUMBER and then apply CEILING:

CEILING(bl.change_dt-LAST_ACT_DT)/(cast(365 as number)/12))
Enthusiast

Re: Trying to ROUND UP

There is a DBS setting that is defaulted to rounding down (false) when shipped.  I can't remember the exact name but it's like "..roundmaghalfwayup" or something to that effect. 

Regards,

Gary