April 4, 2008 at 12:15 pm
i'm writing a query to select rows based on the number of decimal places. i want to use
something like excel's TRUNC function, but not sure if this is available for sql 2005.
any ideas?
basically trying to get all rows which has a number beyond 2 decimal places.
example:
000.11
000.1135 <-- part of the result set
000.11239 <-- part of the result set
can this be done?
***************************************
Fyi; here's the TRUNC for Excel:
Truncates a number to an integer by removing the fractional part of the number.
Syntax
TRUNC(number,num_digits)
Number is the number you want to truncate.
Num_digits is a number specifying the precision of the truncation. The default value for num_digits is 0 (zero).
Remark
TRUNC and INT are similar in that both return integers. TRUNC removes the fractional part of the number. INT rounds numbers down to the nearest integer based on the value of the fractional part of the number. INT and TRUNC are different only when using negative numbers: TRUNC(-4.3) returns -4, but INT(-4.3) returns -5 because -5 is the lower number.
Example:
Formula Description (Result)
=TRUNC(8.9) Integer part of 8.9 (8)
=TRUNC(-8.9) Integer part of -8.9 (-8)
=TRUNC(PI()) Integer part of pi (3)
***************************************
April 4, 2008 at 12:25 pm
Please see 'ceiling' and 'floor' function in bol..
This will do what you want..
NJ
April 4, 2008 at 12:33 pm
Cast(number as int) will do what you need.
Examples:
cast(8.7 as int) = 8
cast(-8.7 as int) = -8
cast(pi() as int) = 3
Convert will do the same, but has reversed syntax:
convert(int, 8.7)
convert(int, -8.7)
convert(int, pi())
Floor is similar but takes integer below the number.
floor(8) = 8
floor(8.7) = 8
floor(-8.7) = -9
Ceiling is the opposite of floor.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 4, 2008 at 12:36 pm
thanks for the quick feedback.
perhaps i don't understand this yet in it's entirety:
"The CEILING function returns the smallest integer greater than or equal to the specified numeric expression. The FLOOR function returns the largest integer less than or equal to the specified numeric expression. For example, in considering a numeric expression of 12.9273, CEILING returns 13 and FLOOR returns 12. The return value of both FLOOR and CEILING has the same data type as the input numeric expression."
so if i select 'ceiling' of this value 12.9273 the result set is 13
vice versa
if i select the 'floor' of this value 12.9273 the result set is 12
how do i set it up to return rows that have more than 2 numbers to the right of the decimal .00 ?
select * from mytable where NUMBERS_COLUMN = ceiling > .00 not sure i understand how this works exactly. forgive me if this is a silly question.
April 4, 2008 at 12:40 pm
And for the other part of your question, if you want numbers that have more than 2 decimal places:
where number * 100 > floor(number * 100)
Use Floor in this case, because it will handle negative numbers correctly for this comparison.
For example:
cast(-8.234 as int) * 100 = -800
cast(-8.234 * 100 as int) = -823
-8.234 * 100 = -823.4
Since -823 is greater than -823.4, you'll get a wrong answer on those.
But Floor works for this comparison.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 4, 2008 at 12:42 pm
i think i'm causing some confusion. i'll try to clear things up a bit.
i have a column full of numeric values. the column datatype is decimal (25,10)
if i wanted to write a query to return all rows whose scale was greater than .00 then it
would return all rows simply because the 'scale' is setup to 10 as stated above in the datatype.
what i'm trying to do is get all the rows whose decimal value is represented as having a number
3 places to the right of the decimal. not just zero's which is how it appears to sql based on the
scale.
does this make sense?
April 4, 2008 at 12:44 pm
GSquared... thanks for the explanation. i'm trying it out actually.
and... the results are in!
GSquared; you are much smarter than i am 🙂
it worked great... reeeaaalllyyy appreciate it.
April 4, 2008 at 1:50 pm
You're welcome. (And thank you for the compliment.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply