Storing Numbers

obrienkev

Hi all,

How do I store a number in this format... 2.00

Using SQL SERVER

Thanks.

Kevin.

TobyRT

Create a field of type DECIMAL (3,2) - that would give you 1-999.00

Obviously if your values are never going to be greater than 9.99 you could create it (1,2)

[edit] - oops - now I've finished telling you how to suck eggs, I'll read the post properly and see that you've said SQL server and run away looking sheepish and apologetic.

TF_TheMoon

If its a currency field, on SQL Server you'd tend to use money or smallmoney

obrienkev

Hi again.

How do I store in SQL SERVER with 2 decimal places?

I set Data Type to Decimal with Scale set to 4 and Precision 19. But this doesn't work.

Any ideas? Thanks.

TF_TheMoon

You need to set the scale to 2 not 4.

Scale specifies the maximum number of decimal digits that can be stored to the right of the decimal point.

So what you want is DECIMAL(19,2)

Shit4Brains

just use FLOAT. it'll handle any number

Sxooter

Shit4Brains wrote:
just use FLOAT. it'll handle any number

And, round off the numbers and cause any accounting package to just plain "get it wrong" over time.

Floats have their uses. Numerics do too.

Numerics store the number EXACTLY as you put it in, and can work on arbitrarily large numbers. But they're slow.

Floats store a number in a converted binary format that may or may not be exatly the same as what you gave it, and can only operate on numbers that are so many digits. They operate on "large" numbers by using exponentiation, but can't operate on long numbers because they chop off the extra.

csn

Sxooter wrote:
Floats have their uses.

Such as? I've never found a use for floats. Their imprecision seems useless to me - can't compare them, calculate them, etc.

Sxooter

They're useful for doing math FAST. generally, floats can do complex mathematical work orders of magnitude faster than numerics, because of their imprecision. OTOH, they're generally considered foot guns amongst those of us who do accounting in a database.

So, if you're doing thousands or millions of calcs for a geospatial system of some kind, floats are a great choice.

If you're doing the payroll with them, you should be lead to the exit of the building and have your ID card snapped in half.

🙂

csn

Hmm, yeah actually I think I did use floats to store lat/long coords. IIRC, floats have a guaranteed precision of how ever many digits, and for the app I was doing 6 significant digits was plenty. I've never noticed any performance problems using numerics, at least none that I'm aware of.

I wonder if they use mysql for payroll at Mysql-AB? ;P And floats too? ;P

Sxooter

Hehe. MySQL running on innodb tables with numerics should be fine for payroll. I'd still choose a different db (I'm sure you already knew that) But honestly, with proper testing and all that, mysql/innodb should be fine. As long as innodb is still a part of mysql.

I do know that Microsoft, at least until recently, ran all their payroll on an AS/400 machine.

If you get a chance, write some simple benchmarks where you do a lot of simple math on floats and numerics and compare. generally the floats will win. However, some databases use some shortcuts to speed things up that will keep reasonably small numerics in the same range as ints / floats.

They're really two different types for two different things. Generally, the magnitude of a float can be quite high at little or no cost, while the precision of a numeric can be quite high, but it always costs, cause you're always storing the whole number.

No matter how big a numeric gets, (i.e. a number like 123897612398762349876234982736423) you'll either get an overflow, or the exact same number back out of the system. With a float, you'd get something like 1.23897612398E26 or something like that.

csn

Yeah, but if they'd used Mysql for their scheme in Office Space, they could've simply blamed it on the database if they got caught! Hmm... 😉