my value 10000.15 is becoming 10000.20

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/

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.

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/

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.

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))

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources