Hi,
Is there any difference between the Decimal and Numeric data type? I am
converting an Oracle Numeric data type (using precision and scale) to
SQLServer and need to know if they are identical, is one there just for
backward compatibility etc.
Thanks for any help.> Is there any difference between the Decimal and Numeric data type?
They are functionally equivalent.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||In ANSI SQL, for DECIMAL, you can get higher precision than what you asked f
or. For NUMERIC, you
should get what you ask for.
In SQL Server, they are identical, you get what you ask for for both datatyp
es.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ClaireB" <ClaireB@.discussions.microsoft.com> wrote in message
news:0291C14E-C9DD-4B95-8095-77B408927262@.microsoft.com...
> Hi,
> Is there any difference between the Decimal and Numeric data type? I am
> converting an Oracle Numeric data type (using precision and scale) to
> SQLServer and need to know if they are identical, is one there just for
> backward compatibility etc.
> Thanks for any help.
>|||Thanks for your help. NUMERIC it is then...
Claire
"Tibor Karaszi" wrote:
> In ANSI SQL, for DECIMAL, you can get higher precision than what you asked
for. For NUMERIC, you
> should get what you ask for.
> In SQL Server, they are identical, you get what you ask for for both datat
ypes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ClaireB" <ClaireB@.discussions.microsoft.com> wrote in message
> news:0291C14E-C9DD-4B95-8095-77B408927262@.microsoft.com...
>
>
Showing posts with label decimal. Show all posts
Showing posts with label decimal. Show all posts
Friday, March 30, 2012
Numeric Representation of Data
In MS Access, for numeric fields, the decimal places shown can be defined as "Auto" meaning that the database will determine the number of decimal places to show based on the content of the field (i.e. 1.0, 0.75, 1.125).
In SQL Server for the same field, it appears that decimal precision is hard coded resulting in a fixed representation (i.e. 1.000, 0.750, 1.125)
Is there a way to make the decimal representation in SQL Server more like Access where trailing zeros are truncated?
See SQL Server 2005 Books Online topic:
float and real (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms173773.aspx
|||Perfect.
thanks for your help.
Wednesday, March 28, 2012
Numeric format - International settings
Hey,
I have following problem :
When is make a query in the enterprice manager I receive all numbers in an
european way (decimal = , no thousand separator is shown ex 1000,52).
When I make the same query in the query analyser i receive the output as
follows 1000.5200
I want it in an european format : 1000,52.
Is there a way to declare this in the query (like you can do with the
datatimes : set dateformat dmy).
I have the same problem when I export the data via a DTS. Can I not say to
him that he have to export numbers in a european format?
Thanks in advance
jacJac,
Try the Query Analyzer setting Tools|Options|Connections Tab|"Use
regional options when displaying currency, numbers, dates, and times."
The display in Query Analyzer will then conform to the Control Panel
settings for Regional display options.
Steve Kass
Drew University
jac wrote:
>Hey,
>I have following problem :
>When is make a query in the enterprice manager I receive all numbers in an
>european way (decimal = , no thousand separator is shown ex 1000,52).
>When I make the same query in the query analyser i receive the output as
>follows 1000.5200
>I want it in an european format : 1000,52.
>Is there a way to declare this in the query (like you can do with the
>datatimes : set dateformat dmy).
>I have the same problem when I export the data via a DTS. Can I not say to
>him that he have to export numbers in a european format?
>Thanks in advance
>jac
>|||Thanks Steve.
But how I say to my DTS (makes a query select * from XXX and export it in a
textfile) that he has to use the regional settings?
Thanks
"Steve Kass" wrote:
> Jac,
> Try the Query Analyzer setting Tools|Options|Connections Tab|"Use
> regional options when displaying currency, numbers, dates, and times."
> The display in Query Analyzer will then conform to the Control Panel
> settings for Regional display options.
> Steve Kass
> Drew University
> jac wrote:
>
>
I have following problem :
When is make a query in the enterprice manager I receive all numbers in an
european way (decimal = , no thousand separator is shown ex 1000,52).
When I make the same query in the query analyser i receive the output as
follows 1000.5200
I want it in an european format : 1000,52.
Is there a way to declare this in the query (like you can do with the
datatimes : set dateformat dmy).
I have the same problem when I export the data via a DTS. Can I not say to
him that he have to export numbers in a european format?
Thanks in advance
jacJac,
Try the Query Analyzer setting Tools|Options|Connections Tab|"Use
regional options when displaying currency, numbers, dates, and times."
The display in Query Analyzer will then conform to the Control Panel
settings for Regional display options.
Steve Kass
Drew University
jac wrote:
>Hey,
>I have following problem :
>When is make a query in the enterprice manager I receive all numbers in an
>european way (decimal = , no thousand separator is shown ex 1000,52).
>When I make the same query in the query analyser i receive the output as
>follows 1000.5200
>I want it in an european format : 1000,52.
>Is there a way to declare this in the query (like you can do with the
>datatimes : set dateformat dmy).
>I have the same problem when I export the data via a DTS. Can I not say to
>him that he have to export numbers in a european format?
>Thanks in advance
>jac
>|||Thanks Steve.
But how I say to my DTS (makes a query select * from XXX and export it in a
textfile) that he has to use the regional settings?
Thanks
"Steve Kass" wrote:
> Jac,
> Try the Query Analyzer setting Tools|Options|Connections Tab|"Use
> regional options when displaying currency, numbers, dates, and times."
> The display in Query Analyzer will then conform to the Control Panel
> settings for Regional display options.
> Steve Kass
> Drew University
> jac wrote:
>
>
Numeric format - International settings
Hey,
I have following problem :
When is make a query in the enterprice manager I receive all numbers in an
european way (decimal = , no thousand separator is shown ex 1000,52).
When I make the same query in the query analyser i receive the output as
follows 1000.5200
I want it in an european format : 1000,52.
Is there a way to declare this in the query (like you can do with the
datatimes : set dateformat dmy).
I have the same problem when I export the data via a DTS. Can I not say to
him that he have to export numbers in a european format?
Thanks in advance
jacJac,
Try the Query Analyzer setting Tools|Options|Connections Tab|"Use
regional options when displaying currency, numbers, dates, and times."
The display in Query Analyzer will then conform to the Control Panel
settings for Regional display options.
Steve Kass
Drew University
jac wrote:
>Hey,
>I have following problem :
>When is make a query in the enterprice manager I receive all numbers in an
>european way (decimal = , no thousand separator is shown ex 1000,52).
>When I make the same query in the query analyser i receive the output as
>follows 1000.5200
>I want it in an european format : 1000,52.
>Is there a way to declare this in the query (like you can do with the
>datatimes : set dateformat dmy).
>I have the same problem when I export the data via a DTS. Can I not say to
>him that he have to export numbers in a european format?
>Thanks in advance
>jac
>|||Thanks Steve.
But how I say to my DTS (makes a query select * from XXX and export it in a
textfile) that he has to use the regional settings?
Thanks
"Steve Kass" wrote:
> Jac,
> Try the Query Analyzer setting Tools|Options|Connections Tab|"Use
> regional options when displaying currency, numbers, dates, and times."
> The display in Query Analyzer will then conform to the Control Panel
> settings for Regional display options.
> Steve Kass
> Drew University
> jac wrote:
> >Hey,
> >
> >I have following problem :
> >When is make a query in the enterprice manager I receive all numbers in an
> >european way (decimal = , no thousand separator is shown ex 1000,52).
> >
> >When I make the same query in the query analyser i receive the output as
> >follows 1000.5200
> >I want it in an european format : 1000,52.
> >
> >Is there a way to declare this in the query (like you can do with the
> >datatimes : set dateformat dmy).
> >
> >I have the same problem when I export the data via a DTS. Can I not say to
> >him that he have to export numbers in a european format?
> >
> >Thanks in advance
> >jac
> >
> >
>
I have following problem :
When is make a query in the enterprice manager I receive all numbers in an
european way (decimal = , no thousand separator is shown ex 1000,52).
When I make the same query in the query analyser i receive the output as
follows 1000.5200
I want it in an european format : 1000,52.
Is there a way to declare this in the query (like you can do with the
datatimes : set dateformat dmy).
I have the same problem when I export the data via a DTS. Can I not say to
him that he have to export numbers in a european format?
Thanks in advance
jacJac,
Try the Query Analyzer setting Tools|Options|Connections Tab|"Use
regional options when displaying currency, numbers, dates, and times."
The display in Query Analyzer will then conform to the Control Panel
settings for Regional display options.
Steve Kass
Drew University
jac wrote:
>Hey,
>I have following problem :
>When is make a query in the enterprice manager I receive all numbers in an
>european way (decimal = , no thousand separator is shown ex 1000,52).
>When I make the same query in the query analyser i receive the output as
>follows 1000.5200
>I want it in an european format : 1000,52.
>Is there a way to declare this in the query (like you can do with the
>datatimes : set dateformat dmy).
>I have the same problem when I export the data via a DTS. Can I not say to
>him that he have to export numbers in a european format?
>Thanks in advance
>jac
>|||Thanks Steve.
But how I say to my DTS (makes a query select * from XXX and export it in a
textfile) that he has to use the regional settings?
Thanks
"Steve Kass" wrote:
> Jac,
> Try the Query Analyzer setting Tools|Options|Connections Tab|"Use
> regional options when displaying currency, numbers, dates, and times."
> The display in Query Analyzer will then conform to the Control Panel
> settings for Regional display options.
> Steve Kass
> Drew University
> jac wrote:
> >Hey,
> >
> >I have following problem :
> >When is make a query in the enterprice manager I receive all numbers in an
> >european way (decimal = , no thousand separator is shown ex 1000,52).
> >
> >When I make the same query in the query analyser i receive the output as
> >follows 1000.5200
> >I want it in an european format : 1000,52.
> >
> >Is there a way to declare this in the query (like you can do with the
> >datatimes : set dateformat dmy).
> >
> >I have the same problem when I export the data via a DTS. Can I not say to
> >him that he have to export numbers in a european format?
> >
> >Thanks in advance
> >jac
> >
> >
>
Numeric format - International settings
Hey,
I have following problem :
When is make a query in the enterprice manager I receive all numbers in an
european way (decimal = , no thousand separator is shown ex 1000,52).
When I make the same query in the query analyser i receive the output as
follows 1000.5200
I want it in an european format : 1000,52.
Is there a way to declare this in the query (like you can do with the
datatimes : set dateformat dmy).
I have the same problem when I export the data via a DTS. Can I not say to
him that he have to export numbers in a european format?
Thanks in advance
jac
Jac,
Try the Query Analyzer setting Tools|Options|Connections Tab|"Use
regional options when displaying currency, numbers, dates, and times."
The display in Query Analyzer will then conform to the Control Panel
settings for Regional display options.
Steve Kass
Drew University
jac wrote:
>Hey,
>I have following problem :
>When is make a query in the enterprice manager I receive all numbers in an
>european way (decimal = , no thousand separator is shown ex 1000,52).
>When I make the same query in the query analyser i receive the output as
>follows 1000.5200
>I want it in an european format : 1000,52.
>Is there a way to declare this in the query (like you can do with the
>datatimes : set dateformat dmy).
>I have the same problem when I export the data via a DTS. Can I not say to
>him that he have to export numbers in a european format?
>Thanks in advance
>jac
>
|||Thanks Steve.
But how I say to my DTS (makes a query select * from XXX and export it in a
textfile) that he has to use the regional settings?
Thanks
"Steve Kass" wrote:
> Jac,
> Try the Query Analyzer setting Tools|Options|Connections Tab|"Use
> regional options when displaying currency, numbers, dates, and times."
> The display in Query Analyzer will then conform to the Control Panel
> settings for Regional display options.
> Steve Kass
> Drew University
> jac wrote:
>
sql
I have following problem :
When is make a query in the enterprice manager I receive all numbers in an
european way (decimal = , no thousand separator is shown ex 1000,52).
When I make the same query in the query analyser i receive the output as
follows 1000.5200
I want it in an european format : 1000,52.
Is there a way to declare this in the query (like you can do with the
datatimes : set dateformat dmy).
I have the same problem when I export the data via a DTS. Can I not say to
him that he have to export numbers in a european format?
Thanks in advance
jac
Jac,
Try the Query Analyzer setting Tools|Options|Connections Tab|"Use
regional options when displaying currency, numbers, dates, and times."
The display in Query Analyzer will then conform to the Control Panel
settings for Regional display options.
Steve Kass
Drew University
jac wrote:
>Hey,
>I have following problem :
>When is make a query in the enterprice manager I receive all numbers in an
>european way (decimal = , no thousand separator is shown ex 1000,52).
>When I make the same query in the query analyser i receive the output as
>follows 1000.5200
>I want it in an european format : 1000,52.
>Is there a way to declare this in the query (like you can do with the
>datatimes : set dateformat dmy).
>I have the same problem when I export the data via a DTS. Can I not say to
>him that he have to export numbers in a european format?
>Thanks in advance
>jac
>
|||Thanks Steve.
But how I say to my DTS (makes a query select * from XXX and export it in a
textfile) that he has to use the regional settings?
Thanks
"Steve Kass" wrote:
> Jac,
> Try the Query Analyzer setting Tools|Options|Connections Tab|"Use
> regional options when displaying currency, numbers, dates, and times."
> The display in Query Analyzer will then conform to the Control Panel
> settings for Regional display options.
> Steve Kass
> Drew University
> jac wrote:
>
sql
numeric data types in sqlserver
Whats the difference between int,float and decimal in sql server.
WHich data type takes how many bytes?
What data type i should use to store the following sets of data
1set--100000,854275,74892734
2set--6538726.98765923,762.659325
Thanks.int is for integers. 4-byte. Max value is 2^31. This looks adequate for your first set although if the numbers will exceed 2^31 you should use bigint (8 bytes)
float is for floating point numbers (non-integers). You will need floating point for the second set. I would suggest the double type for that set.
decimal, numeric, and float all support customizable precision levels. BOL has pretty good documentation on this.
WHich data type takes how many bytes?
What data type i should use to store the following sets of data
1set--100000,854275,74892734
2set--6538726.98765923,762.659325
Thanks.int is for integers. 4-byte. Max value is 2^31. This looks adequate for your first set although if the numbers will exceed 2^31 you should use bigint (8 bytes)
float is for floating point numbers (non-integers). You will need floating point for the second set. I would suggest the double type for that set.
decimal, numeric, and float all support customizable precision levels. BOL has pretty good documentation on this.
numeric and decimal
I am using SQL 2000. What is the difference between a numeric type and
decimal type data? Are they interchangeable?Hi,
In SQL Server, the numeric data type is equivalent to the decimal data type.
Thanks
Hari
SQL Server MVP
" -00Eric Clapton" <a@.b.com> wrote in message
news:enCIlrmuFHA.2396@.TK2MSFTNGP14.phx.gbl...
>I am using SQL 2000. What is the difference between a numeric type and
>decimal type data? Are they interchangeable?
>
decimal type data? Are they interchangeable?Hi,
In SQL Server, the numeric data type is equivalent to the decimal data type.
Thanks
Hari
SQL Server MVP
" -00Eric Clapton" <a@.b.com> wrote in message
news:enCIlrmuFHA.2396@.TK2MSFTNGP14.phx.gbl...
>I am using SQL 2000. What is the difference between a numeric type and
>decimal type data? Are they interchangeable?
>
numeric and decimal
I am using SQL 2000. What is the difference between a numeric type and
decimal type data? Are they interchangeable?Hi,
In SQL Server, the numeric data type is equivalent to the decimal data type.
Thanks
Hari
SQL Server MVP
" -00Eric Clapton" <a@.b.com> wrote in message
news:enCIlrmuFHA.2396@.TK2MSFTNGP14.phx.gbl...
>I am using SQL 2000. What is the difference between a numeric type and
>decimal type data? Are they interchangeable?
>
decimal type data? Are they interchangeable?Hi,
In SQL Server, the numeric data type is equivalent to the decimal data type.
Thanks
Hari
SQL Server MVP
" -00Eric Clapton" <a@.b.com> wrote in message
news:enCIlrmuFHA.2396@.TK2MSFTNGP14.phx.gbl...
>I am using SQL 2000. What is the difference between a numeric type and
>decimal type data? Are they interchangeable?
>
numeric and decimal
I am using SQL 2000. What is the difference between a numeric type and
decimal type data? Are they interchangeable?
Hi,
In SQL Server, the numeric data type is equivalent to the decimal data type.
Thanks
Hari
SQL Server MVP
" -00Eric Clapton" <a@.b.com> wrote in message
news:enCIlrmuFHA.2396@.TK2MSFTNGP14.phx.gbl...
>I am using SQL 2000. What is the difference between a numeric type and
>decimal type data? Are they interchangeable?
>
decimal type data? Are they interchangeable?
Hi,
In SQL Server, the numeric data type is equivalent to the decimal data type.
Thanks
Hari
SQL Server MVP
" -00Eric Clapton" <a@.b.com> wrote in message
news:enCIlrmuFHA.2396@.TK2MSFTNGP14.phx.gbl...
>I am using SQL 2000. What is the difference between a numeric type and
>decimal type data? Are they interchangeable?
>
Monday, March 19, 2012
Number formatting has me stumped
Hi, I dont seem to be able to output a number that has 3 decimal places.
The data source returns eg. 12.564 % and the output format on the report
is ##.###.
The output value though shows 12.56%, I want to show the figure above.
What am I missing or doing wrong? Is there a rounding switch or option I am
missing ?
Thanks in advance .Try this:
#,##0.000
returns:
50.123
0.000
15,050.123
15.050,123 (for example in german systems)
"PaulQld" <PaulQld@.discussions.microsoft.com> schrieb im Newsbeitrag
news:7C955125-6CD3-49DF-9028-D0AD04D2457E@.microsoft.com...
> Hi, I dont seem to be able to output a number that has 3 decimal places.
> The data source returns eg. 12.564 % and the output format on the report
> is ##.###.
> The output value though shows 12.56%, I want to show the figure above.
> What am I missing or doing wrong? Is there a rounding switch or option I
> am
> missing ?
> Thanks in advance .|||BTW, more information on number format strings can be found on MSDN:
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jens Konerow" <keineangabe@.web.de> wrote in message
news:Ow%23cGYqwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> Try this:
> #,##0.000
> returns:
> 50.123
> 0.000
> 15,050.123
> 15.050,123 (for example in german systems)
> "PaulQld" <PaulQld@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:7C955125-6CD3-49DF-9028-D0AD04D2457E@.microsoft.com...
>> Hi, I dont seem to be able to output a number that has 3 decimal places.
>> The data source returns eg. 12.564 % and the output format on the report
>> is ##.###.
>> The output value though shows 12.56%, I want to show the figure above.
>> What am I missing or doing wrong? Is there a rounding switch or option I
>> am
>> missing ?
>> Thanks in advance .
>|||In the Textbox Properties under Format, select Custom.
Use "N3" for numbers, or "P3" for percentages. You will see the example
change to the format you are trying to get.
"PaulQld" wrote:
> Hi, I dont seem to be able to output a number that has 3 decimal places.
> The data source returns eg. 12.564 % and the output format on the report
> is ##.###.
> The output value though shows 12.56%, I want to show the figure above.
> What am I missing or doing wrong? Is there a rounding switch or option I am
> missing ?
> Thanks in advance .
The data source returns eg. 12.564 % and the output format on the report
is ##.###.
The output value though shows 12.56%, I want to show the figure above.
What am I missing or doing wrong? Is there a rounding switch or option I am
missing ?
Thanks in advance .Try this:
#,##0.000
returns:
50.123
0.000
15,050.123
15.050,123 (for example in german systems)
"PaulQld" <PaulQld@.discussions.microsoft.com> schrieb im Newsbeitrag
news:7C955125-6CD3-49DF-9028-D0AD04D2457E@.microsoft.com...
> Hi, I dont seem to be able to output a number that has 3 decimal places.
> The data source returns eg. 12.564 % and the output format on the report
> is ##.###.
> The output value though shows 12.56%, I want to show the figure above.
> What am I missing or doing wrong? Is there a rounding switch or option I
> am
> missing ?
> Thanks in advance .|||BTW, more information on number format strings can be found on MSDN:
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jens Konerow" <keineangabe@.web.de> wrote in message
news:Ow%23cGYqwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> Try this:
> #,##0.000
> returns:
> 50.123
> 0.000
> 15,050.123
> 15.050,123 (for example in german systems)
> "PaulQld" <PaulQld@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:7C955125-6CD3-49DF-9028-D0AD04D2457E@.microsoft.com...
>> Hi, I dont seem to be able to output a number that has 3 decimal places.
>> The data source returns eg. 12.564 % and the output format on the report
>> is ##.###.
>> The output value though shows 12.56%, I want to show the figure above.
>> What am I missing or doing wrong? Is there a rounding switch or option I
>> am
>> missing ?
>> Thanks in advance .
>|||In the Textbox Properties under Format, select Custom.
Use "N3" for numbers, or "P3" for percentages. You will see the example
change to the format you are trying to get.
"PaulQld" wrote:
> Hi, I dont seem to be able to output a number that has 3 decimal places.
> The data source returns eg. 12.564 % and the output format on the report
> is ##.###.
> The output value though shows 12.56%, I want to show the figure above.
> What am I missing or doing wrong? Is there a rounding switch or option I am
> missing ?
> Thanks in advance .
NUMBER FORMATTING : round it to nearest whole number
HI,
CAN ANY ONE HELP me for this query.
i want to format the text field which takes numbers.I want to display the number as with no decimal places, round it to nearest whole number. For example,3.1 to 3.4 will round to 3 and 3.5 to 3.9 will round to 4.Have you tried using the Math.Round function?
Ex: Math.Round(YourVar, 0)
I think that'd do it, but it's been awhile since I've played with that function.
- T
"SRIRAM" wrote:
> HI,
> CAN ANY ONE HELP me for this query.
> i want to format the text field which takes numbers.I want to display the number as with no decimal places, round it to nearest whole number. For example,3.1 to 3.4 will round to 3 and 3.5 to 3.9 will round to 4.
CAN ANY ONE HELP me for this query.
i want to format the text field which takes numbers.I want to display the number as with no decimal places, round it to nearest whole number. For example,3.1 to 3.4 will round to 3 and 3.5 to 3.9 will round to 4.Have you tried using the Math.Round function?
Ex: Math.Round(YourVar, 0)
I think that'd do it, but it's been awhile since I've played with that function.
- T
"SRIRAM" wrote:
> HI,
> CAN ANY ONE HELP me for this query.
> i want to format the text field which takes numbers.I want to display the number as with no decimal places, round it to nearest whole number. For example,3.1 to 3.4 will round to 3 and 3.5 to 3.9 will round to 4.
Number Formats - Why does excel show 12.% instad of 12.0%?
I have a percentage cell within a matrix. I want this cell to be formatted to 1 decimal place, so I use the format of ##0.#% (After trying P1 etc). The cell appears formatted ok within the report viewer:
100%
12.9%
However after exporting to Excel the following occurs:
100.% - There is no 0 after the decimal point (but there is a decimal point . )
12.9% - Works ok when the the trailing digit is not a 0
Any ideas?
format: ##0.0%Monday, March 12, 2012
number format to 2 decimals
How can I format my data to 2 decimal numbers in sql ?
select numericColumn from table1
ex :-
numericColumn
10.22
20.00
30.45
12.02
regards
ypul
ypul wrote:
> How can I format my data to 2 decimal numbers in sql ?
> select numericColumn from table1
> ex :-
> numericColumn
> --
> 10.22
> 20.00
> 30.45
> 12.02
> regards
> ypul
What data type are you using in the table?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||numericcolumn datatype is float
ypul
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23cf2O3PnFHA.2472@.TK2MSFTNGP15.phx.gbl...
> ypul wrote:
> What data type are you using in the table?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||ypul wrote:
> numericcolumn datatype is float
> ypul
>
Probably using the wrong data type. Why have you chosen to use an
approximate data type like float/real instead of DECIMAL/NUMERIC which
stores an exact represenation of a number? Do you really need to store.
From BOL:
"Approximate number data types for use with floating point numeric data.
Floating point data is approximate; not all values in the data type
range can be precisely represented."
If you either must use a float or cannot change the data type to
something more appropriate, you have two options:
1- Format the numeric data on the client - client formatting is
preferred over using SQL Server to do the same
2- Use the CAST function, but you may have to deal with rounding issues
For example:
Declare @.f real
Set @.f = .05
Select @.f -- Returns 5.5500001E-2
Select CAST(@.f as NUMERIC(10, 2)) -- Returns .06
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thanks Buddy
that was very very .....helpful !!
ypul
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:e8NGR%23SnFHA.2860@.TK2MSFTNGP15.phx.gbl...
> ypul wrote:
> Probably using the wrong data type. Why have you chosen to use an
> approximate data type like float/real instead of DECIMAL/NUMERIC which
> stores an exact represenation of a number? Do you really need to store.
> From BOL:
> "Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type
> range can be precisely represented."
> If you either must use a float or cannot change the data type to
> something more appropriate, you have two options:
> 1- Format the numeric data on the client - client formatting is
> preferred over using SQL Server to do the same
> 2- Use the CAST function, but you may have to deal with rounding issues
> For example:
> Declare @.f real
> Set @.f = .05
> Select @.f -- Returns 5.5500001E-2
> Select CAST(@.f as NUMERIC(10, 2)) -- Returns .06
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
select numericColumn from table1
ex :-
numericColumn
10.22
20.00
30.45
12.02
regards
ypul
ypul wrote:
> How can I format my data to 2 decimal numbers in sql ?
> select numericColumn from table1
> ex :-
> numericColumn
> --
> 10.22
> 20.00
> 30.45
> 12.02
> regards
> ypul
What data type are you using in the table?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||numericcolumn datatype is float
ypul
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23cf2O3PnFHA.2472@.TK2MSFTNGP15.phx.gbl...
> ypul wrote:
> What data type are you using in the table?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||ypul wrote:
> numericcolumn datatype is float
> ypul
>
Probably using the wrong data type. Why have you chosen to use an
approximate data type like float/real instead of DECIMAL/NUMERIC which
stores an exact represenation of a number? Do you really need to store.
From BOL:
"Approximate number data types for use with floating point numeric data.
Floating point data is approximate; not all values in the data type
range can be precisely represented."
If you either must use a float or cannot change the data type to
something more appropriate, you have two options:
1- Format the numeric data on the client - client formatting is
preferred over using SQL Server to do the same
2- Use the CAST function, but you may have to deal with rounding issues
For example:
Declare @.f real
Set @.f = .05
Select @.f -- Returns 5.5500001E-2
Select CAST(@.f as NUMERIC(10, 2)) -- Returns .06
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thanks Buddy
that was very very .....helpful !!
ypul
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:e8NGR%23SnFHA.2860@.TK2MSFTNGP15.phx.gbl...
> ypul wrote:
> Probably using the wrong data type. Why have you chosen to use an
> approximate data type like float/real instead of DECIMAL/NUMERIC which
> stores an exact represenation of a number? Do you really need to store.
> From BOL:
> "Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type
> range can be precisely represented."
> If you either must use a float or cannot change the data type to
> something more appropriate, you have two options:
> 1- Format the numeric data on the client - client formatting is
> preferred over using SQL Server to do the same
> 2- Use the CAST function, but you may have to deal with rounding issues
> For example:
> Declare @.f real
> Set @.f = .05
> Select @.f -- Returns 5.5500001E-2
> Select CAST(@.f as NUMERIC(10, 2)) -- Returns .06
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Number format do not work when exported to excel?
I have a report where I have formated the numbers with #.#. When I run this
repot this works well and give me the decimal only when it is needed.
However, when I export to excel, I get te decimal point even when it is not
needed. For example the when I have the whole number 100 it displays as 100.
in excel.
I could use the N1, but I do not want 100.0 to display either.
Any help would be appreciated.
p.s. I am using a matrix if that makes a difference. I have not noticed
this when exporting a table, although I don't know it doesn't happen.On Mar 22, 8:58 am, bugfish69 <bugfis...@.discussions.microsoft.com>
wrote:
> I have a report where I have formated the numbers with #.#. When I run this
> repot this works well and give me the decimal only when it is needed.
> However, when I export to excel, I get te decimal point even when it is not
> needed. For example the when I have the whole number 100 it displays as 100.
> in excel.
> I could use the N1, but I do not want 100.0 to display either.
> Any help would be appreciated.
> p.s. I am using a matrix if that makes a difference. I have not noticed
> this when exporting a table, although I don't know it doesn't happen.
It sounds like this is a defect in the export to Excel functionality.
Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||How about using a conditional formating expression to supress the trailing
zero:
=iif(Fields!.<FieldName>.Value - Fix(Fields!.<FieldName>.Value = 0, "#",
"#.0")
"EMartinez" wrote:
> On Mar 22, 8:58 am, bugfish69 <bugfis...@.discussions.microsoft.com>
> wrote:
> > I have a report where I have formated the numbers with #.#. When I run this
> > repot this works well and give me the decimal only when it is needed.
> > However, when I export to excel, I get te decimal point even when it is not
> > needed. For example the when I have the whole number 100 it displays as 100.
> > in excel.
> >
> > I could use the N1, but I do not want 100.0 to display either.
> >
> > Any help would be appreciated.
> >
> > p.s. I am using a matrix if that makes a difference. I have not noticed
> > this when exporting a table, although I don't know it doesn't happen.
> It sounds like this is a defect in the export to Excel functionality.
> Sorry I could not be of more assistance.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Thanks for the tip. It solved the problem in this instance, however it does
get messy when the filed is a calculated field and not just a field name.
"Bruce Johnson [MSFT]" wrote:
> How about using a conditional formating expression to supress the trailing
> zero:
> =iif(Fields!.<FieldName>.Value - Fix(Fields!.<FieldName>.Value = 0, "#",
> "#.0")
>
> "EMartinez" wrote:
> > On Mar 22, 8:58 am, bugfish69 <bugfis...@.discussions.microsoft.com>
> > wrote:
> > > I have a report where I have formated the numbers with #.#. When I run this
> > > repot this works well and give me the decimal only when it is needed.
> > > However, when I export to excel, I get te decimal point even when it is not
> > > needed. For example the when I have the whole number 100 it displays as 100.
> > > in excel.
> > >
> > > I could use the N1, but I do not want 100.0 to display either.
> > >
> > > Any help would be appreciated.
> > >
> > > p.s. I am using a matrix if that makes a difference. I have not noticed
> > > this when exporting a table, although I don't know it doesn't happen.
> >
> > It sounds like this is a defect in the export to Excel functionality.
> > Sorry I could not be of more assistance.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. SQL Server Developer
> >
> >
repot this works well and give me the decimal only when it is needed.
However, when I export to excel, I get te decimal point even when it is not
needed. For example the when I have the whole number 100 it displays as 100.
in excel.
I could use the N1, but I do not want 100.0 to display either.
Any help would be appreciated.
p.s. I am using a matrix if that makes a difference. I have not noticed
this when exporting a table, although I don't know it doesn't happen.On Mar 22, 8:58 am, bugfish69 <bugfis...@.discussions.microsoft.com>
wrote:
> I have a report where I have formated the numbers with #.#. When I run this
> repot this works well and give me the decimal only when it is needed.
> However, when I export to excel, I get te decimal point even when it is not
> needed. For example the when I have the whole number 100 it displays as 100.
> in excel.
> I could use the N1, but I do not want 100.0 to display either.
> Any help would be appreciated.
> p.s. I am using a matrix if that makes a difference. I have not noticed
> this when exporting a table, although I don't know it doesn't happen.
It sounds like this is a defect in the export to Excel functionality.
Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||How about using a conditional formating expression to supress the trailing
zero:
=iif(Fields!.<FieldName>.Value - Fix(Fields!.<FieldName>.Value = 0, "#",
"#.0")
"EMartinez" wrote:
> On Mar 22, 8:58 am, bugfish69 <bugfis...@.discussions.microsoft.com>
> wrote:
> > I have a report where I have formated the numbers with #.#. When I run this
> > repot this works well and give me the decimal only when it is needed.
> > However, when I export to excel, I get te decimal point even when it is not
> > needed. For example the when I have the whole number 100 it displays as 100.
> > in excel.
> >
> > I could use the N1, but I do not want 100.0 to display either.
> >
> > Any help would be appreciated.
> >
> > p.s. I am using a matrix if that makes a difference. I have not noticed
> > this when exporting a table, although I don't know it doesn't happen.
> It sounds like this is a defect in the export to Excel functionality.
> Sorry I could not be of more assistance.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Thanks for the tip. It solved the problem in this instance, however it does
get messy when the filed is a calculated field and not just a field name.
"Bruce Johnson [MSFT]" wrote:
> How about using a conditional formating expression to supress the trailing
> zero:
> =iif(Fields!.<FieldName>.Value - Fix(Fields!.<FieldName>.Value = 0, "#",
> "#.0")
>
> "EMartinez" wrote:
> > On Mar 22, 8:58 am, bugfish69 <bugfis...@.discussions.microsoft.com>
> > wrote:
> > > I have a report where I have formated the numbers with #.#. When I run this
> > > repot this works well and give me the decimal only when it is needed.
> > > However, when I export to excel, I get te decimal point even when it is not
> > > needed. For example the when I have the whole number 100 it displays as 100.
> > > in excel.
> > >
> > > I could use the N1, but I do not want 100.0 to display either.
> > >
> > > Any help would be appreciated.
> > >
> > > p.s. I am using a matrix if that makes a difference. I have not noticed
> > > this when exporting a table, although I don't know it doesn't happen.
> >
> > It sounds like this is a defect in the export to Excel functionality.
> > Sorry I could not be of more assistance.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. SQL Server Developer
> >
> >
Number Format
Hi all,
I have developed a report in which I need the thousand seperator to be a decimal point.
Instead of presenting the number as 12,923.23 i require that the representation be 12.923,23.
I have tried to customize the the format with the hash value but have had no joy. Any help would be much appreciated.
Ivan
Here's one way to do it. Also if this will be done on the fly, it probably will work best as a user-defined function. You could also nest all of the replace functions for a single select for production, but I did it this way to be more readable.
declare @.test money
Set @.test = '12923.23'
Select Replace(Convert(varchar(50),@.test,1),',','[,]')
Select Replace(Convert(varchar(50),@.test,1),'.','[.]')
Select Replace(Convert(varchar(50),@.test,1),'[,]','.')
Select Replace(Convert(varchar(50),@.test,1),'[.]',',')
print @.test
|||Thanks for the help, its much appreciated.
I ended up sorting out the issue by changing the language property to Italian on the table and entering 'N' in the format property in the desired column.
This then gives the numerical representation which I was looking for.It works because this is the way that the Italians and many other countries represent their numbers.
|||where will i write this code? i right click the textbox carrying the number.From the menu choose properties and then select format tab.Then in the expression area if i write this code , there is an error.Could you help me?|||You must not write these code in expression editor.
you can write a code as a function in vb.net in report code editor. you need to call that fuction in expression editor.
you can impliment custom code to get the desired results
Number Format
Hi all,
I have developed a report in which I need the thousand seperator to be a decimal point.
Instead of presenting the number as 12,923.23 i require that the representation be 12.923,23.
I have tried to customize the the format with the hash value but have had no joy. Any help would be much appreciated.
Ivan
Here's one way to do it. Also if this will be done on the fly, it probably will work best as a user-defined function. You could also nest all of the replace functions for a single select for production, but I did it this way to be more readable.
declare @.test money
Set @.test = '12923.23'
Select Replace(Convert(varchar(50),@.test,1),',','[,]')
Select Replace(Convert(varchar(50),@.test,1),'.','[.]')
Select Replace(Convert(varchar(50),@.test,1),'[,]','.')
Select Replace(Convert(varchar(50),@.test,1),'[.]',',')
print @.test
|||Thanks for the help, its much appreciated.
I ended up sorting out the issue by changing the language property to Italian on the table and entering 'N' in the format property in the desired column.
This then gives the numerical representation which I was looking for.It works because this is the way that the Italians and many other countries represent their numbers.
|||where will i write this code? i right click the textbox carrying the number.From the menu choose properties and then select format tab.Then in the expression area if i write this code , there is an error.Could you help me?|||
You must not write these code in expression editor.
you can write a code as a function in vb.net in report code editor. you need to call that fuction in expression editor.
you can impliment custom code to get the desired results
Subscribe to:
Posts (Atom)