DOUBLE precision for calculations / CONVERT TO DOUBLE?

• bkmooney

  SSCrazy

  Points: 2041

  More actions

  May 19, 2011 at 2:19 pm

  #240638

  I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.

  I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.

  I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.

  For example...

  SELECT CAST(1.0/7.0 AS FLOAT)

  SELECT CONVERT(FLOAT, 1.0/7.0)

  both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.

  But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...

  SELECT CAST(1.0/7.0 AS DOUBLE)

  gives "Incorrect syntax near )"

  SELECT CONVERT(DOUBLE, 1.0/7.0)

  gives "Incorrect syntax near ,"

  Any and all ideas are greatly appreciated!

• Jeff Moden

  SSC Guru

  Points: 1004312

  More actions

  May 19, 2011 at 11:21 pm

  #1327475

  bkmooney (5/19/2011)

  ---

  I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.

  I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.

  I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.

  For example...

  SELECT CAST(1.0/7.0 AS FLOAT)

  SELECT CONVERT(FLOAT, 1.0/7.0)

  both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.

  But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...

  SELECT CAST(1.0/7.0 AS DOUBLE)

  gives "Incorrect syntax near )"

  SELECT CONVERT(DOUBLE, 1.0/7.0)

  gives "Incorrect syntax near ,"

  Any and all ideas are greatly appreciated!

  Don't use float for this. Use the DECIMAL datatype, instead.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  April 26, 2015 at 6:10 am

  #1793508

  For those finding this post through a search engine, like I did...

  A single-precision floating point number is represented as the REAL data type which can also be declared as FLOAT(24).

  A double-precision floating point number is represented as the FLOAT data type which can also be declared as FLOAT(53) or DOUBLE PRECISION.

  More reading

  --this:

  SELECT CAST(1.0/7.0 AS DOUBLE PRECISION);

  -- is equivalent to this:

  SELECT CAST(1.0/7.0 AS FLOAT);

  -- which is equivalent to this:

  SELECT CAST(1.0/7.0 AS FLOAT(53));

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato