Formatting number in sql

How do you convert the following Access query to SQL Server ?
Format([tblA].[PRICE],"0.0000") AS Price
Thanks.SELECT Price = CONVERT(DECIMAL(10,4), tblA.Price)
..
Or, you could let the client application format for you.
"fniles" <fniles@.pfmail.com> wrote in message
news:%23hU7bAp%23FHA.2420@.TK2MSFTNGP12.phx.gbl...
> How do you convert the following Access query to SQL Server ?
> Format([tblA].[PRICE],"0.0000") AS Price
> Thanks.
>|||fniles wrote:

> How do you convert the following Access query to SQL Server ?
> Format([tblA].[PRICE],"0.0000") AS Price
> Thanks.
Access is an application development environment as well as a database.
SQL Server isn't. The client application is what controls how your
numeric values are formatted, not SQL Server. You need to consult the
documentation for whatever client environment you are running.
David Portas
SQL Server MVP
--

Format - currency not working

Hi,
I am using reporting services 2000. Setting the format to currency (C) is
not showing the formatted price.
Is this a known problem?What is your value expression for the textbox?
Make sure that the datatype expected for the field is a numeric value.
If the format property doesn't work, set the value of your textbox to
the following expression:
=Format(Fields!MyField.Value, "C")
or
=FormatCurrency(Fields!MyField.Value)
Regards,
Thiago Silva
Gaurav wrote:
> Hi,
> I am using reporting services 2000. Setting the format to currency (C) is
> not showing the formatted price.
> Is this a known problem?|||Thanks Thiago... I was using a Varchar. Changing to numeric did the trick!!
"tafs7" wrote:
> What is your value expression for the textbox?
> Make sure that the datatype expected for the field is a numeric value.
> If the format property doesn't work, set the value of your textbox to
> the following expression:
> =Format(Fields!MyField.Value, "C")
> or
> =FormatCurrency(Fields!MyField.Value)
> Regards,
> Thiago Silva
>
> Gaurav wrote:
> > Hi,
> >
> > I am using reporting services 2000. Setting the format to currency (C) is
> > not showing the formatted price.
> >
> > Is this a known problem?
>

FORMAT

Hi! I have a query that goes like:
SELECT price * profit AS unit_price FROM tbl_price WHERE id_item = 10
Then, I use this unit_price as a displayMember property of a VB.Net combo
box. The problem is that I have some thing like unit_price = 10.189800, I
would like to have only 2 decimals. How can I format the returned field
right in the query? I know there is a FORMAT function in access, but it
seems not to be in MSDE... Anyone!? I really need to format this, since
it's pretty lame to display a price qwith 6 decimals... ya know well
thank yall! I'll be around, if you have any question!
Jean Christophe Avard
Try this:
SELECT CAST(price * profit as decimal(10,2)) AS unit_price FROM tbl_price
WHERE id_item = 10
Nedi Freguglia
"Jean Christophe Avard" wrote:

> Hi! I have a query that goes like:
> SELECT price * profit AS unit_price FROM tbl_price WHERE id_item = 10
> Then, I use this unit_price as a displayMember property of a VB.Net combo
> box. The problem is that I have some thing like unit_price = 10.189800, I
> would like to have only 2 decimals. How can I format the returned field
> right in the query? I know there is a FORMAT function in access, but it
> seems not to be in MSDE... Anyone!? I really need to format this, since
> it's pretty lame to display a price qwith 6 decimals... ya know well
> thank yall! I'll be around, if you have any question!
> Jean Christophe Avard
>
>
|||Hi,
Use convert function with numeric data type.
SELECT convert(numeric(10,2),price * profit) AS unit_price FROM tbl_price
WHERE id_item = 10
Thanks
Hari
SQL Server MVP
"Jean Christophe Avard" <NO.SP@.M.XXXXX> wrote in message
news:uHlBeNWrFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi! I have a query that goes like:
> SELECT price * profit AS unit_price FROM tbl_price WHERE id_item = 10
> Then, I use this unit_price as a displayMember property of a VB.Net combo
> box. The problem is that I have some thing like unit_price = 10.189800, I
> would like to have only 2 decimals. How can I format the returned field
> right in the query? I know there is a FORMAT function in access, but it
> seems not to be in MSDE... Anyone!? I really need to format this, since
> it's pretty lame to display a price qwith 6 decimals... ya know well
> thank yall! I'll be around, if you have any question!
> Jean Christophe Avard
>
|||On Tue, 30 Aug 2005 09:01:02 -0400, "Jean Christophe Avard" <NO.SP@.M.XXXXX>
wrote:

> Hi! I have a query that goes like:
> SELECT price * profit AS unit_price FROM tbl_price WHERE id_item = 10
> Then, I use this unit_price as a displayMember property of a VB.Net combo
> box. The problem is that I have some thing like unit_price = 10.189800, I
> would like to have only 2 decimals. How can I format the returned field
> right in the query? I know there is a FORMAT function in access, but it
> seems not to be in MSDE... Anyone!? I really need to format this, since
> it's pretty lame to display a price qwith 6 decimals... ya know well
> thank yall! I'll be around, if you have any question!
> Jean Christophe Avard
Download the SQL Server Books Online, you'll find a wealth of information
in it, most of which applies to MSDE as well.
http://www.microsoft.com/sql/techinf...000/books.mspx
Then look up the ROUND function in it.
If you want to return a numeric string from your query instead of a rounded
floating point value, combine ROUND with CONVERT.
|||THANK YOU ALL DUDES!!!
Thank you Luc for the online book!
"Jean Christophe Avard" <NO.SP@.M.XXXXX> wrote in message
news:uHlBeNWrFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi! I have a query that goes like:
> SELECT price * profit AS unit_price FROM tbl_price WHERE id_item = 10
> Then, I use this unit_price as a displayMember property of a VB.Net combo
> box. The problem is that I have some thing like unit_price = 10.189800, I
> would like to have only 2 decimals. How can I format the returned field
> right in the query? I know there is a FORMAT function in access, but it
> seems not to be in MSDE... Anyone!? I really need to format this, since
> it's pretty lame to display a price qwith 6 decimals... ya know well
> thank yall! I'll be around, if you have any question!
> Jean Christophe Avard
>