Solved

Decimal Data Type comparision in SQL server 2005

Posted on 2011-09-19
8
356 Views
Last Modified: 2012-05-12
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 = '>='
    Begin
      If @FieldOne >= @FieldTwo set @ReturnVal =1
    End

Open in new window

0
Comment
Question by:RekhaShah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 36564758
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' ...
0
 
LVL 2

Expert Comment

by:ramkihardy
ID: 36564772
Hello RekhaShah

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.
0
 

Author Comment

by:RekhaShah
ID: 36564776
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?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:RekhaShah
ID: 36564796
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?
0
 

Author Closing Comment

by:RekhaShah
ID: 36564859
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
0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36564872
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'
0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36564882
even this works
-- Test '-1040.78','456.47'
create proc Test
@strV1 nvarchar(10),
@strV2 nvarchar(10)
as
if convert(decimal(18,2),@strV1) > = convert(decimal(18,2),@strV2)
      print convert(nvarchar,@strV1) + ' is greater'
else
      print convert(nvarchar,@strV2) + ' is greater'
0
 

Author Comment

by:RekhaShah
ID: 36566862
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.
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question