RekhaShah
asked on
Decimal Data Type comparision in SQL server 2005
Hi Experts,
I want to compare decimal data type in SQL sever 2005 stored procedure. My parameters are varchar(50) type. So I cast it to decimal(18,2). and then compare the two to see if one is bigger then the other. But for -ve numbers it does not work.
If I compare Field1(-1040.78) >= field2(456.47), it still returns true, obviously, field one is smaller! How do I handle it? Attached is the sample code. Please advise. Appreciate your prompt reply.
I want to compare decimal data type in SQL sever 2005 stored procedure. My parameters are varchar(50) type. So I cast it to decimal(18,2). and then compare the two to see if one is bigger then the other. But for -ve numbers it does not work.
If I compare Field1(-1040.78) >= field2(456.47), it still returns true, obviously, field one is smaller! How do I handle it? Attached is the sample code. Please advise. Appreciate your prompt reply.
set @FieldOne = (select (cast(@FieldOne as decimal(18,2))) as 'Converted')
set @FieldTwo = (select (cast(@FieldTwo as decimal(18,2))) as 'Converted')
If @Opoeration = '>='
Begin
If @FieldOne >= @FieldTwo set @ReturnVal =1
End
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
User can select any two fields of this table and use any comparison operator like >,>=,<.<=,<>,= etc.
I have a few date fields and a few decimal fields, rest of them are varchar fields. So, I was trying to fit in to one comparison routine, like I have shown in my question. I guess, I will have to write different if statements depending on the variable names?
I have a few date fields and a few decimal fields, rest of them are varchar fields. So, I was trying to fit in to one comparison routine, like I have shown in my question. I guess, I will have to write different if statements depending on the variable names?
ASKER
I am not using absolute numbers, I use the variables from the table. If I use the numbers, it works for me too.
I was hoping , I can use some thing like,
IF cast(@FieldOne as decimal(18,2)) <= cast(@FieldTwo as decimal(18,2)
Is this doable? if so, what is the correct syntax?
I was hoping , I can use some thing like,
IF cast(@FieldOne as decimal(18,2)) <= cast(@FieldTwo as decimal(18,2)
Is this doable? if so, what is the correct syntax?
ASKER
Thanks a million. I was just looking for a short cut. This is what I did...
Declare @Money1 as decimal(18,2)
declare @Money2 as decimal(18,2)
SET @ReturnVal=0
set @Money1 = cast( @FieldOne as decimal(18,2))
set @Money2 = cast( @FieldTwo as decimal(18,2))
IF @Operation = '<'
BEGIN
IF @Money1 < @Money2 SET @ReturnVal = 1
END
IF @Operation ='<='
BEGIN
IF @Money1 <= @Money2 SET @ReturnVal = 1
END... and so on
Declare @Money1 as decimal(18,2)
declare @Money2 as decimal(18,2)
SET @ReturnVal=0
set @Money1 = cast( @FieldOne as decimal(18,2))
set @Money2 = cast( @FieldTwo as decimal(18,2))
IF @Operation = '<'
BEGIN
IF @Money1 < @Money2 SET @ReturnVal = 1
END
IF @Operation ='<='
BEGIN
IF @Money1 <= @Money2 SET @ReturnVal = 1
END... and so on
This works fine for me.
declare @strV1 nvarchar(10) = '-1040.78',
@strV2 nvarchar(10) = '456.47'
declare @dV1 decimal(18,2) = @strV1,
@dV2 decimal(18,2) = @strV2
if @dV1 > = @dV2
print convert(nvarchar,@dV1) + ' is greater'
else
print convert(nvarchar,@dV2) + ' is greater'
declare @strV1 nvarchar(10) = '-1040.78',
@strV2 nvarchar(10) = '456.47'
declare @dV1 decimal(18,2) = @strV1,
@dV2 decimal(18,2) = @strV2
if @dV1 > = @dV2
print convert(nvarchar,@dV1) + ' is greater'
else
print convert(nvarchar,@dV2) + ' is greater'
even this works
-- Test '-1040.78','456.47'
create proc Test
@strV1 nvarchar(10),
@strV2 nvarchar(10)
as
if convert(decimal(18,2),@str V1) > = convert(decimal(18,2),@str V2)
print convert(nvarchar,@strV1) + ' is greater'
else
print convert(nvarchar,@strV2) + ' is greater'
-- Test '-1040.78','456.47'
create proc Test
@strV1 nvarchar(10),
@strV2 nvarchar(10)
as
if convert(decimal(18,2),@str
print convert(nvarchar,@strV1) + ' is greater'
else
print convert(nvarchar,@strV2) + ' is greater'
ASKER
Yes, i should have used 'Convert' instead of 'Cast'. I will try it tonight.
if you notice, in my question, i did ask if,
IF cast(@FieldOne as decimal(18,2)) <= cast(@FieldTwo as decimal(18,2) was doable. I just needed to use convert instead of cast!
I could have split the points had i not accepted the answer.
Thanks Sachin.
if you notice, in my question, i did ask if,
IF cast(@FieldOne as decimal(18,2)) <= cast(@FieldTwo as decimal(18,2) was doable. I just needed to use convert instead of cast!
I could have split the points had i not accepted the answer.
Thanks Sachin.
IF -108.40 >= 40.13
BEGIN
PRINT 'true'
END
ELSE
PRINT 'false'
I use this in my sql server 2005 but get the correct answer which is false. i think while you converting negative numbers the negative sign got missed. check that
Regards
Ramki.