Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

The real datatype in SQL Server is precision 7 yet sometimes Excel shows more precision than this, and sometimes completely different decimal parts. Can someone please explain these results:

**Number Inserted | Displayed in SSMS | Displayed in Excel**
_________________________________________________________
12345678             1.234568E+07        12345678
1234567.1            1234567             1234567.125
1234567.5            1234568             1234567.5
1234567.45           1234568             1234567.5
1234567.59           1234568             1234567.625
1234567.69           1234568             1234567.75
1.12345678           1.123457            1.123456836
1.5123456            1.512346            1.512345552
90000000             9E+07               90000000
99999999             1E+08               100000000
share|improve this question

1 Answer 1

up vote 0 down vote accepted

Simply, the SSMS display is formatted to 7 significant figures, with exponential notation for numbers greater than 10,000,000.

The Excel display is probably displaying double precision, and for these purposes (nearly) the actual binary values stored by the "precision 7" numbers.

share|improve this answer
    
Why doesn't SQL Server simply reject values having > 7 significant digits? This would be much better than having applications displaying patently wrong numbers (1234567.59 != 1234567.625) –  Graeme Oct 30 '12 at 15:11
    
You need to read this and other similar floating point references, and use a fixed point type if that is your need. –  Mark Hurd Oct 30 '12 at 15:23
    
Plenty reading there - thanks. –  Graeme Oct 30 '12 at 18:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.