how to show columns with two decimal places

HI ,

I've table whose output is some coumns. The colums show 6 digits after decimal point . My problem is i want to show only two digits after decimal point .. How to do using query..

Thanks

From your question i seem that you are having the FLOAT datatype. You can do this in two ways

1) Modify the table column to DECIMAL(10,2), if you always want to have results in the 2 deciaml place

or

2) SELECT cast (2.456576 AS decimal(10,2)), if you want temp. solution.

Note: This will Round-off the values.

Gurpreet S. Gill

|||

You can use the following approaches,

With Rounded Value:

select convert(Numeric(7,2),ColumnName) From TableName

here it will round/adjust your number to 2 digit decimal

Without Rounded Value:

Select Convert(Numeric(7,2), Round(ColumnName,2,1)) From TableName

here it will truncate the number upto 2 digit decimal and it only show the 2 digit dicimal on your output

|||

Two best practices:

1. The best thing to do is change the table to store the data as the datatype you want to use.

2. If the data needs to be stored like this, but you want to display it this way, the absolute best way of doing this is to use the front end to format the data as you want it. The front end already has to work to display the data in some format, so specifying it as you desire will be best

If you need to do it in T-SQL, then the other replies to use CAST is the next best thing.

|||Already my table datatype is fixed i can't change the datatype. I'm just show output in gridview ...Tell me something which can be done in Grid view or else i've change the whole datatype of table|||As suggested by others, i too says "the best place to do this is the FRONT END". As, when the front end read the data from Database, it internally converts the data into its readable form.

ok tell us which Language you are using & its version ?
which Grid ? and other information. like connection type ...etc.

Gurpreet S. Gill|||Hi i'm using asp.net Grid View (vb.net)|||This may help you
http://www.netomatix.com/development/GridViewDataFormatting.aspx

More reading regarding the "DataFormatString"
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.boundfield.dataformatstring.aspx
http://devauthority.com/blogs/sskokku/archive/2006/08/17/1962.aspx

Gurpreet S.Gill|||More Reading
this the exactly what you want
http://forums.asp.net/thread/1463906.aspx
http://msconline.maconstate.edu/Tutorials/ASPNET2/ASPNET07/aspnet07-01.aspx

Gurpreet S. Gil

how to show columns with two decimal places

HI ,

I've table whose output is some coumns. The colums show 6 digits after decimal point . My problem is i want to show only two digits after decimal point .. How to do using query..

Thanks

From your question i seem that you are having the FLOAT datatype. You can do this in two ways

1) Modify the table column to DECIMAL(10,2), if you always want to have results in the 2 deciaml place

or

2) SELECT cast (2.456576 AS decimal(10,2)), if you want temp. solution.

Note: This will Round-off the values.

Gurpreet S. Gill

|||

You can use the following approaches,

With Rounded Value:

select convert(Numeric(7,2),ColumnName) From TableName

here it will round/adjust your number to 2 digit decimal

Without Rounded Value:

Select Convert(Numeric(7,2), Round(ColumnName,2,1)) From TableName

here it will truncate the number upto 2 digit decimal and it only show the 2 digit dicimal on your output

|||

Two best practices:

1. The best thing to do is change the table to store the data as the datatype you want to use.

2. If the data needs to be stored like this, but you want to display it this way, the absolute best way of doing this is to use the front end to format the data as you want it. The front end already has to work to display the data in some format, so specifying it as you desire will be best

If you need to do it in T-SQL, then the other replies to use CAST is the next best thing.

|||Already my table datatype is fixed i can't change the datatype. I'm just show output in gridview ...Tell me something which can be done in Grid view or else i've change the whole datatype of table|||As suggested by others, i too says "the best place to do this is the FRONT END". As, when the front end read the data from Database, it internally converts the data into its readable form.

ok tell us which Language you are using & its version ?
which Grid ? and other information. like connection type ...etc.

Gurpreet S. Gill|||Hi i'm using asp.net Grid View (vb.net)|||This may help you
http://www.netomatix.com/development/GridViewDataFormatting.aspx

More reading regarding the "DataFormatString"
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.boundfield.dataformatstring.aspx
http://devauthority.com/blogs/sskokku/archive/2006/08/17/1962.aspx

Gurpreet S.Gill|||More Reading
this the exactly what you want
http://forums.asp.net/thread/1463906.aspx
http://msconline.maconstate.edu/Tutorials/ASPNET2/ASPNET07/aspnet07-01.aspx

Gurpreet S. Gilsql

How to SETUP MSDTC on Win2k3

I have documentation for Win2K which says run
comclust.exe to add MSDTC . Running the same brings up
the Admin window.
Please point me to some documentation which clearly
describes on how to setup MSDTC on win3K cluster
Thanks
http://msmvps.com/clustering/archive.../25/16672.aspx
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Alfred" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4bb81$6c5ac5c0$a301280a@.phx.gbl...
>I have documentation for Win2K which says run
> comclust.exe to add MSDTC . Running the same brings up
> the Admin window.
> Please point me to some documentation which clearly
> describes on how to setup MSDTC on win3K cluster
> Thanks
|||Hello Alfred,
Here are the two Microsoft Knowledge Base articles that you need to setup MSDTC on a Win2K3 cluster
How to configure Microsoft Distributed Transaction Coordinator (MSDTC)on a Windows Server 2003
http://support.microsoft.com/?kbid=301600
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/default.aspx?kbid=817064
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

How to set up Report Model Datasource for proper deployment to WSS

Does anyone know how to set up the Connection string for the Datasource
inside Business Intelligence Studio to point to a Model deployed to a
Sharepoint WSS Deeply integrated Report Server. Also I am getting strange 260
character limit errors reported when I use the previous existing DataSource
Settings which work for a Native Integration Report Server. Two things
probably related so fixing first should get rid of second hopefully. Thanks
for any help in this matter.Sorted this out for myself already guys.
Pretty obvious really. If anyone else needs to know the answer reply to this
with tell me how, and I will reveal all.
Onwards and upwards
"ezeget.com" wrote:
> Does anyone know how to set up the Connection string for the Datasource
> inside Business Intelligence Studio to point to a Model deployed to a
> Sharepoint WSS Deeply integrated Report Server. Also I am getting strange 260
> character limit errors reported when I use the previous existing DataSource
> Settings which work for a Native Integration Report Server. Two things
> probably related so fixing first should get rid of second hopefully. Thanks
> for any help in this matter.

How to sequentially SORT numbers that also have a letter attached?

Hi
I'm hoping this will be straightforward for you guys to point me in the
right direction.
Friend of mine emailed me asking the following:
We have a table with a house plot number on it, which is varchar(5).
Example values are:
1
1a
1b
1c
2
2a
2b
2c
3
3a
3b
3c
11a
21c
When we sort these they come out as follows:
1
1a
1b
1c
11a
2
2a
2b
2c
21c
3
3a
3b
3c
Is there an easy way to get them to sort as per the first example? I.e.
1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
I thought it'd be simple but not so! Would appreciate your advice on
the simplest way of achieving this please.Try:
declare @.t table
(
x varchar (5) primary key
, y as case when patindex ('%[a-z]', x) = 0 then ''
else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
, z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
)
insert @.t values ('1')
insert @.t values ('1a')
insert @.t values ('1b')
insert @.t values ('1c')
insert @.t values ('2')
insert @.t values ('2a')
insert @.t values ('2b')
insert @.t values ('2c')
insert @.t values ('3')
insert @.t values ('3a')
insert @.t values ('3b')
insert @.t values ('3c')
insert @.t values ('11a')
insert @.t values ('21c')
select x from @.t
order by z, y
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"ALI" <kismet110@.yahoo.co.uk> wrote in message
news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
Hi
I'm hoping this will be straightforward for you guys to point me in the
right direction.
Friend of mine emailed me asking the following:
We have a table with a house plot number on it, which is varchar(5).
Example values are:
1
1a
1b
1c
2
2a
2b
2c
3
3a
3b
3c
11a
21c
When we sort these they come out as follows:
1
1a
1b
1c
11a
2
2a
2b
2c
21c
3
3a
3b
3c
Is there an easy way to get them to sort as per the first example? I.e.
1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
I thought it'd be simple but not so! Would appreciate your advice on
the simplest way of achieving this please.|||Thanks for your help Tom, hopefully this will get them what they want,
I don't feel totally stupid now too for thinking it would be _really_
simple!
Tom Moreau wrote:
> Try:
> declare @.t table
> (
> x varchar (5) primary key
> , y as case when patindex ('%[a-z]', x) = 0 then ''
> else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
> , z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
> else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
> )
> insert @.t values ('1')
> insert @.t values ('1a')
> insert @.t values ('1b')
> insert @.t values ('1c')
> insert @.t values ('2')
> insert @.t values ('2a')
> insert @.t values ('2b')
> insert @.t values ('2c')
> insert @.t values ('3')
> insert @.t values ('3a')
> insert @.t values ('3b')
> insert @.t values ('3c')
> insert @.t values ('11a')
> insert @.t values ('21c')
> select x from @.t
> order by z, y
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "ALI" <kismet110@.yahoo.co.uk> wrote in message
> news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
> Hi
> I'm hoping this will be straightforward for you guys to point me in the
> right direction.
> Friend of mine emailed me asking the following:
> We have a table with a house plot number on it, which is varchar(5).
> Example values are:
> 1
> 1a
> 1b
> 1c
> 2
> 2a
> 2b
> 2c
> 3
> 3a
> 3b
> 3c
> 11a
> 21c
> When we sort these they come out as follows:
> 1
> 1a
> 1b
> 1c
> 11a
> 2
> 2a
> 2b
> 2c
> 21c
> 3
> 3a
> 3b
> 3c
> Is there an easy way to get them to sort as per the first example? I.e.
> 1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
> I thought it'd be simple but not so! Would appreciate your advice on
> the simplest way of achieving this please.