May 26, 2005 at 11:52 am
I have a table "Sales" with a field called "Amount" of "decimal(12,2)" that stores money amount such as "7.50", "50.45", "10.00", "5.00", "2.25", etc. How do I select records where the dollar amount is a whole dollar, such as "1.00", "2.00", etc..? Basically, I want the cent part to be ".00"? Using the "%" mod function doesn't work on "decimal" datatype.
Does anyone know how to do this?
May 26, 2005 at 11:56 am
you can throw in a where condition :
where convert(int,col1) = col1
when you convert to int you lose the decimal part. So if its still equal to the original value then its a whole number.
does that help ?
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
May 26, 2005 at 12:11 pm
I think this may be what your looking for:
create table #temp (amt decimal(5,2))
insert into #temp (amt) values (12.34)
select cast(cast(amt as int)as decimal (5,2) )
from #temp
-------
12.00
(1 row(s) affected)
May 26, 2005 at 12:19 pm
Are you trying to return all rows where the cents part is .00 or are your trying to convert the display of all amount so that they show .00?
May 26, 2005 at 12:25 pm
Display of all amounts where cents = .00
insert into #temp values (14.00)
select * from #temp
Where cast(amt as int) - amt = 0
amt
-------
14.00
(1 row(s) affected)
Edit: I think I like Dinakar Nethi's way better.
select * from #temp
Where cast(amt as int) = amt
May 26, 2005 at 12:45 pm
I'm trying to SELECT all records where the Amount is a whole dollar. I need to exclude all records where the Amount field contains cents. Using Dinakar's suggestion:
SELECT *
FROM Sales
WHERE CAST(Amount AS int) = Amount
appears to give me what I need. I just need to verify that this is the correct syntax to get all "whole dollar" amounts. Thanks.
May 26, 2005 at 8:12 pm
Dan your
SELECT *
FROM Sales
WHERE CAST(Amount AS int) = Amount does not return the correct data. Try the following
HTH Mike
CREATE TABLE #Test
(
pk int identity(1,1),
DecValue decimal(5,2)
)
INSERT INTO #Test VALUES(12.00)--HERE
INSERT INTO #Test VALUES(12.50)
INSERT INTO #Test VALUES(.99)
INSERT INTO #Test VALUES(2.00) --HERE
select A.DecValue AS WholeNumberOnly
From #test a
WHERE right(cast(a.decvalue as decimal(5,2)),2) ='00'
/*
Returns
WholeNumberOnly
---------------
12.00
2.00
*/
--Edit: I agree with Ron I think I like Dinakar Nethi's way better.
--Ron's and Nethi's statements adjusted to run with test data
--Nethi's
--select * from #test
--Where cast(DecValue as int) = DecValue
--Returns
--Pk DecValue
--1 12.00
--4 2.00
--Ron K's
--select * from #Test
--Where cast(DecValue as int) - DecValue = 0
--pk DecValue
----------- --------
--1 12.00
--4 2.00
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply