Skip to main content
Database Administrators

Return to Question

Incorporated comment
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle...

This

Select convert(numeric(19,2),convert(float,9.76499999999999999999999999))

gives 9.76.

And this

Select convert(numeric(19,2),convert(float,9.7649999999999999999999))

gives 9.77.

Can someone explain why one truncates while the other rounds up? This is causing a nightmare for me.

I wish I could just stop using floats but they are all over our application, which has been ongoing for over 30 years.

To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle...

This

Select convert(numeric(19,2),convert(float,9.76499999999999999999999999))

gives 9.76.

And this

Select convert(numeric(19,2),convert(float,9.7649999999999999999999))

gives 9.77.

Can someone explain why one truncates while the other rounds up? This is causing a nightmare for me.

To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle...

This

Select convert(numeric(19,2),convert(float,9.76499999999999999999999999))

gives 9.76.

And this

Select convert(numeric(19,2),convert(float,9.7649999999999999999999))

gives 9.77.

Can someone explain why one truncates while the other rounds up? This is causing a nightmare for me.

I wish I could just stop using floats but they are all over our application, which has been ongoing for over 30 years.

Added a db<>fiddle. Rephrased som excerpts.
Source Link
MDCCL
MDCCL
  • 8.5k
  • 3
  • 31
  • 62

To my surprise, I run the following queries in SQL Server 2014 and they both returneach returns a different resultsresult, as shown in this db<>fiddle...

This

Select convert(numeric(19,2),convert(float,9.76499999999999999999999999))

gives 9.76.

And this

Select convert(numeric(19,2),convert(float,9.7649999999999999999999))

gives 9.77.

Can someone explain why one rounds up andtruncates while the other truncatesrounds up? This is causing a nightmare for me.

To my surprise, I run the following queries in SQL Server 2014 and they both return different results...

This

Select convert(numeric(19,2),convert(float,9.76499999999999999999999999))

gives 9.76.

And

Select convert(numeric(19,2),convert(float,9.7649999999999999999999))

gives 9.77.

Can someone explain why one rounds up and the other truncates? This is causing a nightmare for me.

To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle...

This

Select convert(numeric(19,2),convert(float,9.76499999999999999999999999))

gives 9.76.

And this

Select convert(numeric(19,2),convert(float,9.7649999999999999999999))

gives 9.77.

Can someone explain why one truncates while the other rounds up? This is causing a nightmare for me.

It looks like "SQL" actually means "SQL Server", so I modified accordingly (the datab sublanguage called SQL and the database management systen named SQL Server are very distinct things) and added the tags that apply.
Source Link
MDCCL
MDCCL
  • 8.5k
  • 3
  • 31
  • 62

2 Two conversions of practically the same number giving 2two different results

To my surprise, I run the following queries in SQL Server 2014 and they both return different results...

This

Select convert(numeric(19,2),convert(float,9.76499999999999999999999999))

gives 99.76.76

andAnd

Select convert(numeric(19,2),convert(float,9.7649999999999999999999))

gives 99.77.77

Can someone Explain, Why doesexplain why one roundrounds up and the other truncatetruncates? thisThis is causing a nightmare for me.

2 conversions of practically the same number giving 2 different results

To my surprise I run the following queries in SQL 2014 and they both return different results...

Select convert(numeric(19,2),convert(float,9.76499999999999999999999999))

gives 9.76

and

Select convert(numeric(19,2),convert(float,9.7649999999999999999999))

gives 9.77

Can someone Explain, Why does one round up and the other truncate? this is causing a nightmare for me.

Two conversions of practically the same number giving two different results

To my surprise, I run the following queries in SQL Server 2014 and they both return different results...

This

Select convert(numeric(19,2),convert(float,9.76499999999999999999999999))

gives 9.76.

And

Select convert(numeric(19,2),convert(float,9.7649999999999999999999))

gives 9.77.

Can someone explain why one rounds up and the other truncates? This is causing a nightmare for me.

edited title
Link
JohnG
JohnG
  • 1.1k
  • 2
  • 11
  • 27
Source Link
JohnG
JohnG
  • 1.1k
  • 2
  • 11
  • 27