question

AlexKlap avatar image
AlexKlap asked

Data type trouble

I have data something like this: 70.6 70.60 70.60 70.60 70.70 I can't used varchar as I need to perform arithmetic operations (> or floor). When I used float all records change to 70.7 and 70.6. When I changed to decimal (2,2), then even 70.6 changed to 70.60. Please suggest which data type is best for me. Thanks
sql-server-2008datatypesdecimalfloat
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
If you truly need some values stored as 70.6 and others as 70.60, you will need to use strings. But can you explain why you need that or what exactly you do need? You can store the data using whichever data type is best suited for your data (based on storage size and operation performance) and then format the output differently if needed.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Trailing zeros is just formatting. Do that in the client. There are no trailing zeros in real numbers, or, there are an infinite amount of trailing zeros. Don't try to push formatting of this type into the T-SQL. It doesn't work well. Let the database do the storage and the math, then the formatting can be done by the client tools.
2 Likes 2 ·
Hi Tom, currently varchar has been used in datawarehouse. i am pulling data to mart and written query for data validation. Result6 = CASE WHEN M.Core IS NULL OR FLOOR(LOG10(REVERSE(ABS(M.Core)+1)))+1 <> 2 THEN 'Core' END i convert the datatype to float to make above query work correctly. but when i used float datatype trailing zeros missing from all records. so if actual record has 70.60 it changes to 70.6 and appear in the output. when i used datatype decimal the records like 76.5 changed to 76.50. and getting excluded from output. Please share your thoughts. Thanks.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.