Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 my value 10000.15 is becoming 10000.20

Author  Topic 

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2015-04-23 : 15:26:59
upto 9999.15 it is working fine, when i pass 10000.15 then the result is coming as 10000.20.

for every number suffix we use year which is 15.

Can you please tell me wrong below.
Thanks a lot for the helpful info

Declare @SequenceNO real

set @SequenceNO = 10000.15

print Convert(real,Convert(varchar(50),@SequenceNO))



my result should be 10000.15, but is coming as 10000.20

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-23 : 15:27:51
Simple answer is to not use real/float data type. It is approximate data. Use decimal data type instead.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2015-04-23 : 15:29:57
Hi Tara, can you please tell me what should i use.

Thank you.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-23 : 15:31:10
I did in my edit.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2015-04-23 : 15:32:36
thanks using this:
print cast(@SequenceNO as decimal(10,2))

Thank you.
Go to Top of Page

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2015-04-23 : 15:56:23
Sorry Tara, it worked when just tested with print :

but when i used the logic within my procedure SP, getting error unable to convert varchar to number or decimal which ever i am using getting error: sql string i am building i have been using it for a while.

SET @SQLWHERE = @SQLWHERE + ' AND A.SequenceNO=' + cast(@SequenceNO as numeric(9,2))
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-23 : 16:02:33
You have to convert to varchar to be able to add it to @SQLWHERE. Switch @SequenceNO to decimal in the DECLARE and then CONVERT to varchar to concat to @SQLWHERE.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -