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