473,324 Members | 1,646 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Format Number on Select

I have a price field that when I query it, it returns the price as
"5.0000". What function can I use to return the price as "5.00"?

THANKS
Dec 26 '07 #1
4 28667
Formatting is normally the job of the presentation/reporting layer, where
this is done best.

If the price is numeric then one way is to cast it to the correct decimal
precision (note that rounding will occur):
SELECT CAST(5.0000 AS NUMERIC(10, 2))

If it is character data type, then:
SELECT LEFT('5.0000', LEN('5.0000') - 2),
STUFF('5.0000', LEN('5.0000') - 2, 2, '')

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Dec 26 '07 #2
Erich93063 (er********@gmail.com) writes:
I have a price field that when I query it, it returns the price as
"5.0000". What function can I use to return the price as "5.00"?
str(). The syntax is str(val[, width[, scale]]) if memory serves. Look it up
in Books Online for the exact details.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 26 '07 #3
On Dec 27, 12:30*am, Erich93063 <erich93...@gmail.comwrote:
I have a price field that when I query it, it returns the price as
"5.0000". What function can I use to return the price as "5.00"?

THANKS
Where do you want to show data?
If you use front end application, use format function there
Dec 27 '07 #4
cast( '5.0000' as decimal(9,2))

--
Sincerely,
John K
Knowledgy Consulting, LLC
knowledgy.org

Atlanta's Business Intelligence and Data Warehouse Experts
"Erich93063" <er********@gmail.comwrote in message
news:5c**********************************@t1g2000p ra.googlegroups.com...
>I have a price field that when I query it, it returns the price as
"5.0000". What function can I use to return the price as "5.00"?

THANKS

Jan 9 '08 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Muhd | last post by:
Hey all, I have a basic table that looks something like this. CREATE TABLE MyTable ( ID INT IDENTITY PRIMARY KEY, Company_ID INT NOT NULL, Round VARCHAR(50) NOT NULL, Details VARCHAR(250)...
2
by: Jim Craig | last post by:
When I do a transform with a stylesheet containing the function format-number($varname,'#,##0.00') from XSLerator (which uses MSXML) I get the result I expect, a decimal number rounded to 2 places....
2
by: Richard Haber | last post by:
I am using format-number(-1234.56,'#,##0.00;(#,##0.00)') in a stylesheet that works in every xsl processor (including MSXML2) except for the one in ..NET 1.1. I am expecting to see (1,234.56) as...
1
by: crowl | last post by:
Hi all, I want to have a number in a certain format. I took a look in the msdn and was happy to find the format-number Function. This works fine. But unfortunately, I can't find the format...
1
by: silellak | last post by:
I have found an odd error when using an XSL stylesheet transform. We have a Notes/Domino application with the following line of code in one of the stylesheets: <xsl:value-of...
0
by: codeproblems | last post by:
============= XML content ================== <?xml version="1.0"?> <cdb> <spill> <ctx_volume>142300000</ctx_volume> <date>19670318</date> <shipname>TORREY CANYON</shipname> </spill>...
1
by: Ela | last post by:
Hallo, I have next problem: When I'm traying export data from table from one column formated like longint (21,111,111.00 ) form access 2003 to the xml, number looks diferent...
1
by: RolfK | last post by:
Dear All, can anyone help me with a peace of xslt which converts a number to a binary or hex string ? I even can not find a power operator in xslt / xpath 2.0. Any help is highly welcome as...
4
mafaisal
by: mafaisal | last post by:
Hello All I am Using sql2005 My Doubt is How To Format Numbers in Sql Query Like Format(NOS,"0000") in vb ie I have Table Tbl1 Fields Nos1 Datatype int values 1,2,3,4,................1000 ...
3
ashsa
by: ashsa | last post by:
Hi everyone, I am trying to display a numeric value fetched from an sql server table which is stored in the exponential notation. For eg, 0.08 is getting stored in the table as...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.