Quick One - I Hope Convert/Cast?

[smedvid]

I have a value "10.9899997711" that I am attempting to import into SQL Server from MS Access via OpenRowSet...

The value appears to be converted to 10.99 by default. How can I bring the value in as 10.9899. The table field is Numeric 18,4.

Thanks in Advance!

Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[gmmastros]

the 5th digit is a 9, so when you round to 4 digits, it rounds up, making the 4th digit a 0 and so forth. Seems like it is already doing the right thing.

Would you prefer to truncate at 4 digits?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smedvid]

Presently under DTS the value is brought in as "10.9899". Under T-SQL using OpenRowSet the value is brought in as "10.99".

I would like to have the same value come into the database "10.9899"; and I can not understand why this is happening... Strange..

Any comments appreciated.



Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[gmmastros]

When you are importing to the columns with data type Decimal(18,4), and the value has more precision than you can store, the value is getting rounded. For example...

Declare @Input Float

Set @Input = 10.9899997711

Select @Input,Convert(Decimal(18,4), @Input)

If you want to truncate at 4 digits instead of rounding, you'll need to use some extra code. Something like this:

Declare @Input Float

Set @Input = 10.9899997711

Select @Input As Original,
       Convert(Decimal(18,4), @Input) As [Rounded],
       Floor(@Input * 10000) / 10000 As [truncated]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom