All Forums Analytics
dbmagnus 2 posts Joined 06/15
30 Mar 2016
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.
 

dnoeth 4628 posts Joined 11/04
30 Mar 2016

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))

 

Dieter

LepkoINC 5 posts Joined 02/09
07 Apr 2016

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

You must sign in to leave a comment.