I am inserting 12.10 in a decimal data type that is 18 precision and 4 scale. SQL Server puts in 12.1000 I only want the original only stored in the db - 12.10 What can I do? (i need 18,4)
Reduce the precision of column to 2 Gaurav Moderator Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
12.10 and 12.1 are different for my data. 12 feet 10 inches, 12 feet 1 inch. If I insert 12.1, I want it to stay as 12.1. If I insert 12.10, I want it to stay as 12.10.
<br />you'd need to store it as varchar...<br /><br />feet.inches is not a decimal number, so storing it as such doesn't quite work <img src='/community/emoticons/emotion-5.gif' alt=';-)' /> If you did want it to work then either store 12'1" as 12.01 or store the whole thing in inches ie 145<br /><br />Cheers<br />Twan
You can store the entire data in inches as well.... This idea came from the fact that date is also stored something like this internally.... HTH. Gaurav Moderator Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
what would be best in terms of performance? 2 ints or varchar? there will have to be some conversion here for the web display. Show feet.inches for some, total area for others, etc. (real estate system)
bfarr23, I understand this is a personal decision, but is there a reason for not storing the data in inches as int and then converting it into feets? I would not recommend storing as varchar as this will need explicit calculations while calculating area etc. as you mentioned. Also character comparison is slower than integer comparison. Gaurav Moderator Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Int ( 2 or maybe better one in inches, like Twan suggested) might be a better choice if you need or ever need to perform some calculations. Bambola.
this is the problem. The data orginates from numerours sources that send varying measure unit formats(all inches, feet.inches, etc). This data then gets loaded by a VB program into our database. I need to keep performacne up and be able to display in the measure unit that the data was originally sent in. So if I convert all to inches, I still need to display in the measure unit that data orignated in. I am thinking that the best thing is to convert to one common format that will be displayed most frequently. Thus less conversions. Still have to store the original meaasure unit though. Tricky situation here.
Same reply as above... Use a standard to identify the format of the data when it was recieved and store it in databalse along with data. When displaying, show the data based on the format stored in database., Gaurav Moderator Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
You could add a field to contain the unit, then use a view to calculate it or maybe a calculated column with an index on it. Bambola.
If you need to show the way it was submitted and calculate area then I'd suggest: store it as varchar, so that you can display it back to the user store it as integer converted to inches for your calculations (144 square inches being 1 square foot) Cheers Twan
