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
 General SQL Server Forums
 New to SQL Server Programming
 conversion

Author  Topic 

seeker62
Starting Member

40 Posts

Posted - 2013-04-11 : 10:50:45
datatype in table is numeric(18,0) in an insert in a sp I use
convert(decimal(8,2),InvoiceVolume/2000,0). The data inserted is an int and not a decimal. for instance if InvoiceVolume in table is 8196 what should be inserted from sp is 4.09 but instead data that inserts is 4. Any ideas. Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-04-11 : 10:59:49
InvoiceVolume/2000 is returning an integer first, and then you are cinverting it to decimal, all you need to do is this
convert(decimal(8,2),InvoiceVolume*1.0/2000,0) and you should be good to go.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

seeker62
Starting Member

40 Posts

Posted - 2013-04-11 : 11:31:32
I made suggested change and data is still 4 and not 4.09
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-11 : 11:41:31
If you really want 4.09, then
select Convert(DECIMAL(8, 2), FLOOR(convert(decimal(9,3),InvoiceVolume/2000.0,0) * 100)/100)

But if you want 4.10 then
select convert(decimal(8,2),InvoiceVolume/2000.0,0)
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-11 : 11:49:45
What is the dataype of the table's column, where this data is getting stored? Check if that is int, change it to what you need.

Cheers
MIK
Go to Top of Page

seeker62
Starting Member

40 Posts

Posted - 2013-04-11 : 12:11:05
when i run a select outside of sp with this convert it gives me what i want. when inside the sp it only returns an int.
Go to Top of Page

seeker62
Starting Member

40 Posts

Posted - 2013-04-11 : 12:37:41
thanks guys. it was the datatype in table data stored in that was limiting it.
Go to Top of Page
   

- Advertisement -