Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
trunc function
- Thread starter swoodring
- Start date
According to BOL, there is no exact SQL Server equivalent to the Oracle trunc function. However, the expression that you give looks as if it only requires a greatest integer function, which is "floor" in SQL Server. Please try it and see if it works.
One caution: When rounding down to the nearest integer, "trunc" and "floor" seem to be identical when operating on positive numbers. However they work differently on negative numbers. For example, trunc(-1.2) = -1, whereas floor(-1.2) = -2. Based on the names of your columns, I'm guessing that this is not an issue for you. At least I hope not.
One caution: When rounding down to the nearest integer, "trunc" and "floor" seem to be identical when operating on positive numbers. However they work differently on negative numbers. For example, trunc(-1.2) = -1, whereas floor(-1.2) = -2. Based on the names of your columns, I'm guessing that this is not an issue for you. At least I hope not.
- Thread starter
- #3
actually I was just copying a sample of what I'm trying to do but it has the wrong field name. So your assumptuion on the negative not mattering. Well it will matter. The field I'm using actually does have negative values it a sales figure and sometimes is negative due to returns. This helps - get me close to the # I'm looking at by not quite. Any other ideas?
I doubt that there is a single SQL Server function that will handle both negative and positive numbers the same way as Oracle's trunc. I suggest you write your own.
Based on a quick test, you can combine floor, abs, and sign to get what you need. For example
sign(-1.2)*floor(abs(-1.2)) = -1
sign(1.2)*floor(abs(1.2)) = +1
You can embed this expression in a user defined function. Call it "trunc" and you may not even notice you're using SQL Server!
Based on a quick test, you can combine floor, abs, and sign to get what you need. For example
sign(-1.2)*floor(abs(-1.2)) = -1
sign(1.2)*floor(abs(1.2)) = +1
You can embed this expression in a user defined function. Call it "trunc" and you may not even notice you're using SQL Server!
tlbroadbent
MIS
You could use cast or convert to get the equivalent to TRUNC.
select cast(-1.2 as int), cast(1.2 as int) returns -1, 1
select convert(int, -1.2), convert(int, 1.2) returns -1, 1 Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.
- Status
Similar threads