convert float to nvarchar not working

• sharon-472085

  Ten Centuries

  Points: 1182

  More actions

  December 5, 2016 at 5:42 am

  #319456

  hi all,

  i need help in sql conversion datatype :

  ok first:

  from here everthing fine

  --CREATE TABLE

  CREATE TABLE TBL_CONVERT_TO_NVARCHAR ( TEST_VALUE FLOAT)

  --INSERT TO COLUMN TYPRE FLOAT

  INSERT TBL_CONVERT_TO_NVARCHAR (TEST_VALUE)

  SELECT 32106348

  --CHECK DATA OK

  SELECT * FROM TBL_CONVERT_TO_NVARCHAR

  LOOK AT THIS :

  SELECT CONVERT (NVARCHAR(MAX),TEST_VALUE ) FROM TBL_CONVERT_TO_NVARCHAR

  I GET :

  3.21063e+007 THIS IS NOT EXPECTED 🙂

  WHY 🙂 ?

  --EDITION DETAILS

  Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)

  Oct 20 2015 15:36:27

  Copyright (c) Microsoft Corporation

  Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

  THANK YOU VERY MUCH

  SHARON

• Eirikur Eiriksson

  SSC Guru

  Points: 182915

  More actions

  December 5, 2016 at 5:58 am

  #1915969

  Quick question, why are you using float?

  😎

  The value returned is exactly the same, but in scientific notation. You can change the last parameter of the Convert function which will give you slightly different results

  SELECT CONVERT (NVARCHAR(MAX),TEST_VALUE,1 ) AS CONV_P_1 FROM TBL_CONVERT_TO_NVARCHAR;

  SELECT CONVERT (NVARCHAR(MAX),TEST_VALUE,2 ) AS CONV_P_2 FROM TBL_CONVERT_TO_NVARCHAR;

  SELECT CONVERT (NVARCHAR(MAX),TEST_VALUE,3 ) AS CONV_P_3 FROM TBL_CONVERT_TO_NVARCHAR;

  will return this

  CONV_P_1

  ------------------------

  3.2106348e+007

  CONV_P_2

  ------------------------

  3.210634800000000e+007

  CONV_P_3

  ------------------------

  3.2106348000000000e+007

  Alternatively you can embed an integer conversion in the conversion call

  SELECT CONVERT (NVARCHAR(MAX),CONVERT(INT,TEST_VALUE,0),3 ) FROM TBL_CONVERT_TO_NVARCHAR

  Which will return

  ---------

  32106348

• sharon-472085

  Ten Centuries

  Points: 1182

  More actions

  December 5, 2016 at 6:13 am

  #1915973

  hi ,

  first thank you 🙂

  second i do it for test some of tables with float data that i need to convert to

  char. ( dont look for logic :))

  i still not understand what the probolem to convert float to same vakue as varchar 🙂

  sahron

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  December 5, 2016 at 12:15 pm

  #1916078

  sharon-472085 (12/5/2016)

  ---

  hi ,

  first thank you 🙂

  second i do it for test some of tables with float data that i need to convert to

  char. ( dont look for logic :))

  i still not understand what the probolem to convert float to same vakue as varchar 🙂

  sahron

  There's no problem. Float is an approximate numeric. This means that you don't worry about precision and scientific notation is acceptable. Decimal and Int are exact numerics, using them means that you care about every digit on them.

  Reference: https://msdn.microsoft.com/en-us/library/ms187752.aspx

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2