|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Precision of float and decimal values displayed by Query Analyserdisplayed by float and decimal values can be set as a default? For example, when querying a 3rd Party Database that incorporates XY-coordinates as float datatypes, Query Analyser displays the precision to only 2 or 3 decimal digits. If these floats are cast to varchar then 4 decimal digits then appear. While with Decimal(25) fields which are used for the internal System Identifiers it displays embedded commas (which I would prefer not to have) and it truncates the lower order digits unless cast to varchar. Is there any way to configure Query Analyser to the formats I desire for these particular data types? I thought the precision might have been controlled by the MS XP "Regional and Language Options" settings however testing appears to show this is not the case. I would prefer to be able to set a default rather then configuring each query independently, as I do extensive ad-hoc analysis on this data. Perhaps I am missing something. I am unable to replicate the behavior
you describe using Query Analyzer from SQL Server 2000 with the latest service pack. Can you provide a script that demonstrates the problem? create table Buoyant (f float) insert Buoyant values (1.2345678) insert Buoyant values (9871.2345678) insert Buoyant values (0.00056789) insert Buoyant values (1.23456789) select * from Buoyant f ----------------------------------------------------- 1.2345678 9871.2345678000001 5.6789000000000004E-4 1.2345678899999999 Roy Harvey Beacon Falls, CT On Tue, 25 Jul 2006 03:10:02 -0700, Rocky <Ro***@discussions.microsoft.com> wrote: Show quote >Is it possble to configure Query Analyser so that the precision to be >displayed by float and decimal values can be set as a default? > >For example, when querying a 3rd Party Database that incorporates >XY-coordinates as float datatypes, Query Analyser displays the precision to >only 2 or 3 decimal digits. If these floats are cast to varchar then 4 >decimal digits then appear. While with Decimal(25) fields which are used for >the internal System Identifiers it displays embedded commas (which I would >prefer not to have) and it truncates the lower order digits unless cast to >varchar. > >Is there any way to configure Query Analyser to the formats I desire for >these particular data types? > >I thought the precision might have been controlled by the MS XP "Regional >and Language Options" settings however testing appears to show this is not >the case. > >I would prefer to be able to set a default rather then configuring each >query independently, as I do extensive ad-hoc analysis on this data. Rocky (Ro***@discussions.microsoft.com) writes:
> Is it possble to configure Query Analyser so that the precision to be No. You will have to cater for that in your SELECT statement. Or cut and> displayed by float and decimal values can be set as a default? paste into Excel. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Rocky (Ro***@discussions.microsoft.com) writes:
> For example, when querying a 3rd Party Database that incorporates I should add that what QA does, is that it strips trailing zeroes. So if> XY-coordinates as float datatypes, Query Analyser displays the precision > to only 2 or 3 decimal digits. If these floats are cast to varchar then > 4 decimal digits then appear. While with Decimal(25) fields which are > used for the internal System Identifiers it displays embedded commas > (which I would prefer not to have) and it truncates the lower order > digits unless cast to varchar. a number is 3.25, and this can be represented exactly, then it displays as 3.25. Whereas select convert(float, 1.235) will give you many decimals. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks to each of you for your assistanece, but I have since be able to
contact a SQL Server DBA. After suggesting some convoluted options, he has come back with the cause of the problem. It is actually the enabling of the "Use regional settings to display ..." in the Tools/Options/Connections dialog box that has caused the truncations and embedded commas. Without this being set, the behaviour is as desired. There appears no way to independently set different options, for example to not truncate float values but simultaneously retain commas within numbers. Once again thank you each for your assistance. Show quote "Erland Sommarskog" wrote: > Rocky (Ro***@discussions.microsoft.com) writes: > > For example, when querying a 3rd Party Database that incorporates > > XY-coordinates as float datatypes, Query Analyser displays the precision > > to only 2 or 3 decimal digits. If these floats are cast to varchar then > > 4 decimal digits then appear. While with Decimal(25) fields which are > > used for the internal System Identifiers it displays embedded commas > > (which I would prefer not to have) and it truncates the lower order > > digits unless cast to varchar. > > I should add that what QA does, is that it strips trailing zeroes. So if > a number is 3.25, and this can be represented exactly, then it displays > as 3.25. Whereas > > select convert(float, 1.235) > > will give you many decimals. > > > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > You might look into using NUMERIC types instead of FLOAT. With NUMERIC you
can specify an exact scale (# of digits to the right of the decimal point). FLOAT represents an approximation of a real number. Show quote "Rocky" <Ro***@discussions.microsoft.com> wrote in message news:6354F132-4AC5-4EA3-8348-8ED2C766638D@microsoft.com... > Is it possble to configure Query Analyser so that the precision to be > displayed by float and decimal values can be set as a default? > > For example, when querying a 3rd Party Database that incorporates > XY-coordinates as float datatypes, Query Analyser displays the precision > to > only 2 or 3 decimal digits. If these floats are cast to varchar then 4 > decimal digits then appear. While with Decimal(25) fields which are used > for > the internal System Identifiers it displays embedded commas (which I would > prefer not to have) and it truncates the lower order digits unless cast to > varchar. > > Is there any way to configure Query Analyser to the formats I desire for > these particular data types? > > I thought the precision might have been controlled by the MS XP "Regional > and Language Options" settings however testing appears to show this is not > the case. > > I would prefer to be able to set a default rather then configuring each > query independently, as I do extensive ad-hoc analysis on this data.  |
|||||||||||||||||||||||
Other interesting topics