Datatype truncation

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 19, 2012 at 8:13 am

  #1539589

  Sean Lange (9/19/2012)

  ---

  ChrisM@Work (9/19/2012)

  ---

  Sean Lange (9/7/2012)

  ---

  ChrisM@Work (9/7/2012)

  ---

  sqlnaive (9/7/2012)

  ---

  For such kind of reasons we should avoid float and real datatypes ? And rather use decimal and numeric ?

  Have a look in BOL under CONVERT to see which of these datatypes will implicitly convert to a character datatype.

  It's not a good reason to avoid float and real - it's a good reason to explicitly convert, and to know that implicit conversion can occasionally bite.

  I typically don't use float for a variety of reasons but I thought I would play with this a little bit. I am completely unable to get the float value to cast or convert to a varchar and not truncate it. I was able to cast it first to a numeric and then to a varchar to keep the original number. Of course you have to be careful of the numeric precision or you get rounding errors.

  declare @test-2 float

  select @test-2 = 0.1234123412341234

  select @test-2 as Original, CAST(@test as varchar(50)) as CastVarchar, CONVERT(varchar(50), @test-2) as ConvertVarchar, cast(CAST(@test as numeric(20,16)) as varchar(50)) as Clean, cast(CAST(@test as numeric(20,18)) as varchar(50)) as WithRoundingErrors

  So two questions that maybe somebody can answer. How would you get that float value to a varchar without additional cast/convert? Why does the float value change to a smaller number when the precision increases?

  If you want to convert float to a string datatype, use STR(), that's what it's for:

  declare @test-2 float

  select @test-2 = 0.1234123412341234

  select

  @test-2 as Original,

  CAST(@test as varchar(50)) as CastVarchar,

  CONVERT(varchar(50), @test-2) as ConvertVarchar,

  cast(CAST(@test as numeric(20,16)) as varchar(50)) as Clean,

  cast(CAST(@test as numeric(20,18)) as varchar(50)) as WithRoundingErrors,

  CAST(STR(@test, 18, 16) AS VARCHAR(50)) -- exactly matches @test-2 (STR returns CHAR)

  Ahh yes...if I used floats I would probably remember that function. I knew there had to something. 😉

  Same here, Sean. The only reason I know about this is from a spackle article[/url] written by Jeff Moden some time ago.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  September 19, 2012 at 8:48 am

  #1539610

  Same here, Sean. The only reason I know about this is from a spackle article written by Jeff Moden some time ago.

  Ahh...

  --McCrackis Philled

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/