decimal data type
-
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 inserted - 12.10
What can I do?
(i need 18,4)
-
Just use the CAST, or CONVERT, function to re-format the field when you display it.
eg: CAST(Field as decimal(18,2))
Make sure you put the double brackets on the end, I always forget to
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
I don't understand the problem.
12.1000 is 12.10
It's also 12.1 and 12.100 and 12.10000.
The (18,4) determines how it is represented when displayed.
Cheers,
- Mark
Cheers,
- Mark -
The problem is one of presentation. SQL stores the data in 18,4 format, padding out the zeros, so an entry of 12.10 results in 12.1000 in the database.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
no.
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.
-
Create Function StripRightZero(@Val Decimal(18,4)) Returns VarChar(18)
Begin
Declare @Answ Varchar(18)
Select @Answ=Cast(Cast(@Val as Decimal(18,4)) as Varchar(18))
While Right(@Answ,1)='0'
Set @Answ=Left(@Answ,DataLength(@Answ)-1)
Return @Answ
End
Go
Set NoCount On
Declare @Val Decimal(18,4)
Set @Val=12.1
Select dbo.StripRightZero(@Val)
Set @Val=12.01
Select dbo.StripRightZero(@Val)
Set @Val=12
Select dbo.StripRightZero(@Val)
Drop Function StripRightZero -
Hi bfarr,
quote:
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.
I think you need to rethink your logic.
12.1 and 12.10 and 12.10000....are all the same.
It would be different to use 12.01 and 12.1. Don't kow if this can be applied.
Also changing data types to other numerical type yields nothing, but create only more problems, that is dealing with approximation errors and rounding issues
CREATE TABLE TestType(
a float,
b real,
c decimal(8,4)
)
GO
INSERT INTO TestType (a,b,c) VALUES(12.1, 12.1,12.1)
INSERT INTO TestType (a,b,c) VALUES(12.10, 12.10,12.10)
SELECT * FROM TestTypeWhat about breaking this up into two separate fields?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi bfarr23,
I'd say that the problem lies in definition -one shouldn't ask a program to store non-decimal values in a field of data type decimal. As far as I know, SQL Server does not support feet and inches format, so it seems that the best approach is to find a suitable format that will hold your numbers correctly.
Depending on how data are entered, whether you can influence format of input file/table, and what is done with the data afterwards, you could either use one of Frank's solutions (split into 2 numeric fields or add leading 0 if inches are less than 10), or store the values as e.g. varchar and maybe write a conversion UDF to access them. Anyway, if the values would be stored in any numeric format, you could not use them in computing - because 12.1 feet is not the same as 12 feet and 1 inch... so that IMHO it would be even better NOT to store them as numbers to avoid misinterpretation. Two separate columns seem to be the best solution to me, if that's not possible, then varchar; another possibility would be to convert everything from feet to metric system... Well, it's just my two bits' worth - hope it helps.
Vladan
-
Ok, now that I know what the data represents, I would recommend you go with Valdan's suggestion of two fields. This will give you the ability to show the data as 12 feet 1 inch or 12.1 or 12'1".
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
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)
-
quote:
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.
not really sure as for performance.
I think integers could be retrieved faster because of their fixed length.
However, should be easier to handle calculations in your application layer
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I would do it as two integers. With varchar you might have to convert it to use math functions.
-SQLBill
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply