(urgent)how should i treat signed decimal values in sql server

Hi,

I want to transfer the data from a excel spread sheet to sql server. I have used the oledb comand and it works fine, and i have also used the sqlbulkcopy and it transfers the data properly.

But in my excel spread sheet i have many columns with data as -0.76 or 0.76 or something like that but when it transfer it to sqlserver that particular column in sql server shows it as 0.00762711864406778

So how i can i tell sql server to display the data as 0.76 instead of 0.00762711864406778

any help will be appreciated.

Regards

Karen

Hi,

The only thing I can think of is the cells in excel with your decimal numbers is formated 1. to display 2 decimal places only, whilst the value is much more precise 2. (that's guess) cell is formated as percentage, so after convesion there is decimal point moved 2 places left ....

Hope at least one of these is true

Tomek

|||

I changed the Format from Percentage to general but it still doesnt work.|||

So you've changed cell formating to general it shows in spreadsheet 0.76 and after transfer you get 0.00762711864406778

What db type has destination column, do you use vb(or any other language) or dts to transfer it?

Try to transfer it to flat file and then see which side cause problem.

Tomek

|||

I am using a oledb connection to connect to the excel spreadsheet and then using a sqlbulkcopy to copy it to sql server. Have changed the dbase field from varchar to money and when it was varchar i get 0.007627118464406778 in the db column and when i changed to money it is been stores as 0.0076

Regards

Karen

|||

This is strange, you changed the cell format from percentage to general and the cells still display 0.76? That seems wrong to me. When you change to general, it should change to .0076 right? So all we are left with as a mystery is why you only see .0076 and if you copy it over to SQL Server you see .0076.....otherstuff, right? Did you change it to "general" or "number"? Number and percentage have a box with "decimal places" in it, that specify how many decimal places to show, but general should show all of them up to the width of the box, the rest are truncated just like any other overflowing field. Is this what is happening? If you go to "number" and increase "decimal places" under formatting options to 10 or 50, does it show some of what SQL Server shows you?

If so, you should consider that Excel is different from SQL Server in that Excel is designed to be both front-end and database at the same time, so it understands the idea of truncating data on display implicitly while separately storing the truth. SQL Server does not implicitly do so, you would need to write a stored procedure or a query that explicitly rounds to see rounded numbers, so you might do 'SELECT round(column, 2.) from table' to get that rounding behavior that you desire. If you want a rounded table, meaning that the data itself is truncated, then you can write a quick t-sql statement to truncate the data in your table but that would affect the accuracy of any further calculations you do from that data.

Hope that helps,

John

|||

John,

Thanks for your answer, I am not doing any calculation for this field, it just gonna displayed in a report. so in the report i will multiply the field by 100 and that should give the right result.

Regards

Karen