Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-30-2016
02:03 PM

03-30-2016
02:03 PM

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.

2 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-30-2016
03:31 PM

03-30-2016
03:31 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-07-2016
08:00 AM

04-07-2016
08:00 AM

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