• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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.
set @FieldOne = (select (cast(@FieldOne as decimal(18,2))) as 'Converted')
set @FieldTwo = (select (cast(@FieldTwo as decimal(18,2))) as 'Converted')

If @Opoeration = '>='
      If @FieldOne >= @FieldTwo set @ReturnVal =1

Open in new window

1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if the variable is varchar, the data in it will still be considered varchar, even if it "looks" like decimal.

the set @fieldone = cast( ... as decimal ... ) will actually recast to varchar, as the variable is varchar.

so, you need to get the values into decimal variables in order to get the appropriate comparison

note that with the variable being varchar, '9' would be also >=  '1000000' ...
Hello RekhaShah

IF -108.40 >= 40.13
    PRINT 'true'
    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
RekhaShahAuthor Commented:
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?
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

RekhaShahAuthor Commented:
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?
RekhaShahAuthor Commented:
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 = '<'
           IF @Money1 < @Money2  SET      @ReturnVal = 1
IF      @Operation ='<='
            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'
      print convert(nvarchar,@dV2) + ' is greater'
even this works
-- Test '-1040.78','456.47'
create proc Test
@strV1 nvarchar(10),
@strV2 nvarchar(10)
if convert(decimal(18,2),@strV1) > = convert(decimal(18,2),@strV2)
      print convert(nvarchar,@strV1) + ' is greater'
      print convert(nvarchar,@strV2) + ' is greater'
RekhaShahAuthor Commented:
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.

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now