Formatting Query Data

When I run queries against tables in MSSQL2000 it returns dates with a time component and has way too many zeros after the decimal point.

Is there any functions or something I can put in the query so my data comes back with only a date for the date and only two decimal points for the numbers?You are generally best off leaving the formatting to the code that receives the data from the database. But you can use the Convert function to format a date and the round function for numeric values.

SELECT Convert(varchar,DateField,101),Round(NumericField,2)
FROM YourTable

However, Round may not work quite as you expect. It will round the value but not necessarily format it for display as you might expect. Other date formats are available, check Sql Server Books OnLine under the convert function for the options.|||I agree with McMurdo, however, that the best place to handle this is outside of the database, in your code that is displaying the data.|||I agree, this is what I wanted - the ability to have the data formatted correctly from SQL. I tried for ages to find a way to do that on the basis of what I new about MySQL (which has a lot of functions for this).

But I'll look into it, as ideally I want my data coming in right from the SQL Query, rather than .Net doing it. Indeed, there is another thread in another forum arguing that as people kept giving me the whole Eval solution.|||Can you strip trailing spaces as well - I have a CUSTNMBR that is 5 characters long put it pads it with four spaces to to make it 9 (the length of the field).

I'll investigate the day, so I may have the answer this morning (UK Time).|||Use RTRIM or LTRIM to strip spaces.sql

Formatting numbers question

I have a datagrid with currency amounts, the default currency format "C" shows only two decimal places, I would like to show up to 4 decimal places. Useing the formula/expression editor, how would this be done?Have you tried C4? Currency with 4 numbers of precision. Just an thought, I do not know if it will work or not.|||

I do this in the expression.

=Format(Fields!ThisValue.Value, "$#.####)

Hope this helps.

|||

Both of the above are possible options.

rs12345 - put "c4" into the Format property of the textbox

guyinkalamazoo2 - apply the formatting directly tot he value in the expression of the textbox

Formatting numbers

Hi all,
I would like to know how can one change the thousand and decimal separator.
I tried to change the ReportingServer web.configs <globalization> culture
and uiCulture.
Regardless of the cultures I am always getting US number formats (e.g.
"1,234,567.89").
The format string I use on the report is: "#,##0.00" and according to the
cultures I would expect to get "1.234.567,89".
Please help I am already desperateOn the 'Report' properties there is a Language setting - you need to set
that.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||Hi Tony,
Thanx I missed that property - it works now!!!!
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:efviupE2EHA.3840@.tk2msftngp13.phx.gbl...
> On the 'Report' properties there is a Language setting - you need to set
> that.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>

formatting decimal places in a query in MS SQL

Hey - I have a quick question and know that it is probably pretty simple, but I am stumped. I have a query where I need to make a colum a number that looks like a percent with 2 significant digits:

i.e.,
SELECT tblNumericCovert.number1, tblNumericCovert.number2, [number1]/[number2] AS testDiv
FROM tblNumericCovert

where testDiv needs to spit out results like this ###.##

I am totally lost, if anyone can help, I would appreciate it.cast(cast(YourValue as decimal(10,2)) as varchar(6)) + '%'

But really, data formatting issues should be handled by the interface, not sql server. Do your formatting in Crystal, VB, ASP, Excel, or whatever else you are using to display results.|||It will do the rounding though:

select right('000000'+ltrim(str(123.87745, 6, 2)), 6) + '%'|||Probably better to use ROUND for rounding, so you know what you are trying to accomplish when you look at your code...

SELECT ROUND(127.4355,2) = 127.4400|||Makes sense.|||Originally posted by rdjabarov
Makes sense.
That works perfectly! Thanks. I've always used Access which is a breeze and I'm trying to use SQL more. I really appreciate all of y'alls help on this.

Formatting Currency

I'm trying to display currency with ZERO decimal places... the book that I
bought says to use "C" for currency, with formats it traditionally (with 2
decimal places)
the other option was to use D0 (D-zero), which works as far as the decimal
is concerned, but doesn't add the $
should I concatenate a "$" to a amount in DO format? gotta be a better wayuse C0 ... which is the same as D0 with the "$"
"Derek in Richmond" <DerekinRichmond@.discussions.microsoft.com> wrote in
message news:01169023-D1A9-4DF9-A628-EA21A7696539@.microsoft.com...
> I'm trying to display currency with ZERO decimal places... the book that
I
> bought says to use "C" for currency, with formats it traditionally (with 2
> decimal places)
> the other option was to use D0 (D-zero), which works as far as the decimal
> is concerned, but doesn't add the $
> should I concatenate a "$" to a amount in DO format? gotta be a better
way|||Use the format pattern "C0" instead of "C". Note: any digits to the right of
the decimal place will be rounded.
"Derek in Richmond" wrote:
> I'm trying to display currency with ZERO decimal places... the book that I
> bought says to use "C" for currency, with formats it traditionally (with 2
> decimal places)
> the other option was to use D0 (D-zero), which works as far as the decimal
> is concerned, but doesn't add the $
> should I concatenate a "$" to a amount in DO format? gotta be a better way

Formatting a float variable to 2 decimal places

Hey,

I am filling a temp table with various float variables and I need to format one particular column to 2 decimal places.

Does anyone know the correct syntax to do this, and should it be done before filling the temp table or when I select what I needs from the temp table?

ThanksThis should be done at the application level.|||Ok, I'll look into that|||If at all possible, use decimal (x,2) instead of float. Float is considered an approximate datatype and if you have lots of math/rounding it could introduce errors in your application.

Formating results in Query Analyzer

I have a table as follows:
A NO Balance [decimal (16,5)]
------ ---------------- ------
18.00 001000000000000 -37.982
19.00 002000000000000 -17.656

In the Query analyzer when I try:
SELECT * FROM TABLE1

A NO Balance [decimal (16,5)]
------ ---------------- ------
18.00 001000000000000 -37.98
19.00 002000000000000 -17.66

I need the query analyzer to format them as they are in the tables.
Help please.it's weird
it's if you round up the balance data

try this

select convert(decimal(16,5),balance)|||Did not work! Same results!!!

Originally posted by Karolyn
it's weird
it's if you round up the balance data

try this

select convert(decimal(16,5),balance)|||are you sure that Balance is really stored as -37.982|||Yes, I am sure

Originally posted by Karolyn
are you sure that Balance is really stored as -37.982|||what is you're max precision ?

SELECT @.@.MAX_PRECISION|||38.0

Originally posted by Karolyn
what is you're max precision ?

SELECT @.@.MAX_PRECISION|||where do you see the data -37.982
?|||From the Enterprise Manager

Originally posted by Karolyn
where do you see the data -37.982
?|||SET NUMERIC_ROUNDABORT on
SET ARITHABORT ON

SELECT * FROM TABLE1

does an error occur ?

are you REALLY only doing Select * from Table1 ?|||No error occured,
I am sure 100% that I am doing only a select *!!

Originally posted by Karolyn
SET NUMERIC_ROUNDABORT on
SET ARITHABORT ON

SELECT * FROM TABLE1

does an error occur ?

are you REALLY only doing Select * from Table1 ?|||try contacting BlindMan or Breitt Keiser

Formating numbers in SSIS

I have a Amount field which is declared as Decimal. the data for this will be somethin like this 0.152

output need it to be -

00000.1520

How can I do this?

You'll have to convert it a string and prepend/append the zeroes.

-Jamie

|||Thank You. I converted the field into string and prepended the zero's. but it was too many steps. i wish SSIS had some kind of format function available to do this.|||

Godai B wrote:

Thank You. I converted the field into string and prepended the zero's. but it was too many steps. i wish SSIS had some kind of format function available to do this.

Too many steps? Really?

"0000" + (DT_STR, 20, 1252) 0.1520

That doesn't seem like too many to me. Can you give me examples of format functions like you require?

-Jamie

|||

this field is derived from 2 columns in a sql server table and the datatype is decimal(18,3)

the output format needs to be 000000.0000 .

the input field can have a single digit or upto six digits before the decimal point. if it has a single digit then i need to prepend "00000", if there are 2 digits then i prepend "0000" and so on.

same with digits after the decimal point.

ex: Input -->10.120 Output --> 000010.1200

Input --> .1 Output --> 000000.1000

what i did was, used the findstring function in the derived column to find the decimal point position and then got the predecimal digits and postdecimal digits. then with the help of length function i prepended or appended the zero's and then finally concatenated the predecimal and postdecimal digits.

Hope u can help me with an easy way to do this.

|||

I would use the FINDSTRING function as you have done to get the whole and the mantissa. But instead of using LENGTH I would just do this:

RIGHT("000000" + [wholepart], 6) + "." + REVERSE(RIGHT(REVERSE([mantissapart] + "0000"), 4))

Yeah, maybe a format function would be good.

-Jamie

Formating

I have a field which returns a decimal (3.0000) i only want the whole
value(3) not the parts (.0000) to be displayed in the report.
when i right click on the textbox and select propeties then under the
'format' section i click on 'number' and select '1234' hit 'ok' and preview
the report it still displays 3.000.
how can i display just the 3(whole value as opposed to the part)?
Tia,
VinnySet the format to "F0"
"Vinny Vinn" wrote:
> I have a field which returns a decimal (3.0000) i only want the whole
> value(3) not the parts (.0000) to be displayed in the report.
> when i right click on the textbox and select propeties then under the
> 'format' section i click on 'number' and select '1234' hit 'ok' and preview
> the report it still displays 3.000.
> how can i display just the 3(whole value as opposed to the part)?
> Tia,
> Vinny|||Thank You,it worked!!
"David Siebert" wrote:
> Set the format to "F0"
> "Vinny Vinn" wrote:
> > I have a field which returns a decimal (3.0000) i only want the whole
> > value(3) not the parts (.0000) to be displayed in the report.
> > when i right click on the textbox and select propeties then under the
> > 'format' section i click on 'number' and select '1234' hit 'ok' and preview
> > the report it still displays 3.000.
> > how can i display just the 3(whole value as opposed to the part)?
> >
> > Tia,
> > Vinny

Formating

Hi ,
I want to display the a numeric field column in two decimal
places.
48.00
48.56
But in the Qty is a Number then it is showing as 48.0. How I can get in two
decimal places
Need Help
BabzWhere do you want to display these numbers? What tool are you talking about?
This is an issue with the client tool you are using.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Babz" <notknown@.nodomain.com> wrote in message
news:eL0dzx4lDHA.2080@.TK2MSFTNGP10.phx.gbl...
> Hi ,
> I want to display the a numeric field column in two decimal
> places.
> 48.00
> 48.56
> But in the Qty is a Number then it is showing as 48.0. How I can get in
two
> decimal places
> Need Help
> Babz
>|||Hi
I want this thing in the sql query only.
In the database it is stored as 48.0000. 54.0000, 76.5786.
Even after rounding the value to 2 decimals it is showing in the 4 decimal
places.
I want the data in the following format
48.00
54.00
76.58
Need Help
Babz
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:Oi#15D5lDHA.2616@.TK2MSFTNGP11.phx.gbl...
> Where do you want to display these numbers? What tool are you talking
about?
> This is an issue with the client tool you are using.
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Babz" <notknown@.nodomain.com> wrote in message
> news:eL0dzx4lDHA.2080@.TK2MSFTNGP10.phx.gbl...
> > Hi ,
> > I want to display the a numeric field column in two decimal
> > places.
> >
> > 48.00
> > 48.56
> > But in the Qty is a Number then it is showing as 48.0. How I can get in
> two
> > decimal places
> >
> > Need Help
> >
> > Babz
> >
> >
>|||I strongly suggest you follow Linchi's suggestion and have your client tool to format the data. If
you are saying that you are using Query Analyzer, I cannot imagine the format being relevant, as I
trust that you can understand the difference between a value and the presentation and format of such
a value. I do assume that you don't use Query Analyzer to present data to end users.
Having said that, if you *really* want to do this in QA, you need to convert the data to a datatype
for which QA presents two digits to the right of the decimal point. ROUND does *not* change
datatype, it only changes the value. So, you need to use CAST for this. Example:
SELECT CAST(3.1 AS decimal(5,2))
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Babz" <notknown@.nodomain.com> wrote in message news:OfDZbj5lDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Hi
> I want this thing in the sql query only.
> In the database it is stored as 48.0000. 54.0000, 76.5786.
> Even after rounding the value to 2 decimals it is showing in the 4 decimal
> places.
> I want the data in the following format
> 48.00
> 54.00
> 76.58
> Need Help
> Babz
>
> "Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
> news:Oi#15D5lDHA.2616@.TK2MSFTNGP11.phx.gbl...
> > Where do you want to display these numbers? What tool are you talking
> about?
> > This is an issue with the client tool you are using.
> >
> > --
> > Linchi Shea
> > linchi_shea@.NOSPAMml.com
> >
> >
> > "Babz" <notknown@.nodomain.com> wrote in message
> > news:eL0dzx4lDHA.2080@.TK2MSFTNGP10.phx.gbl...
> > > Hi ,
> > > I want to display the a numeric field column in two decimal
> > > places.
> > >
> > > 48.00
> > > 48.56
> > > But in the Qty is a Number then it is showing as 48.0. How I can get in
> > two
> > > decimal places
> > >
> > > Need Help
> > >
> > > Babz
> > >
> > >
> >
> >
>|||THANX FOR REPLYING. I am using dataset object to fetch the data and binding
it with the data list view. In this case how I can format in the front end.
I Know it is .Net related question but still any body knows this can answer
for this.
Thanx and Regards
Babz
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eeYBLp5lDHA.2364@.TK2MSFTNGP11.phx.gbl...
> I strongly suggest you follow Linchi's suggestion and have your client
tool to format the data. If
> you are saying that you are using Query Analyzer, I cannot imagine the
format being relevant, as I
> trust that you can understand the difference between a value and the
presentation and format of such
> a value. I do assume that you don't use Query Analyzer to present data to
end users.
> Having said that, if you *really* want to do this in QA, you need to
convert the data to a datatype
> for which QA presents two digits to the right of the decimal point. ROUND
does *not* change
> datatype, it only changes the value. So, you need to use CAST for this.
Example:
> SELECT CAST(3.1 AS decimal(5,2))
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Babz" <notknown@.nodomain.com> wrote in message
news:OfDZbj5lDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > Hi
> > I want this thing in the sql query only.
> > In the database it is stored as 48.0000. 54.0000, 76.5786.
> >
> > Even after rounding the value to 2 decimals it is showing in the 4
decimal
> > places.
> >
> > I want the data in the following format
> >
> > 48.00
> > 54.00
> > 76.58
> >
> > Need Help
> >
> > Babz
> >
> >
> >
> > "Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
> > news:Oi#15D5lDHA.2616@.TK2MSFTNGP11.phx.gbl...
> > > Where do you want to display these numbers? What tool are you talking
> > about?
> > > This is an issue with the client tool you are using.
> > >
> > > --
> > > Linchi Shea
> > > linchi_shea@.NOSPAMml.com
> > >
> > >
> > > "Babz" <notknown@.nodomain.com> wrote in message
> > > news:eL0dzx4lDHA.2080@.TK2MSFTNGP10.phx.gbl...
> > > > Hi ,
> > > > I want to display the a numeric field column in two
decimal
> > > > places.
> > > >
> > > > 48.00
> > > > 48.56
> > > > But in the Qty is a Number then it is showing as 48.0. How I can
get in
> > > two
> > > > decimal places
> > > >
> > > > Need Help
> > > >
> > > > Babz
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sorry, I'm not that versed with the GUI elements in .NET. Perhaps some other jumps in here, or you
might want to post a new post with a descriptive subject and also include some relevant .net group
in that post.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Babz" <notknown@.nodomain.com> wrote in message news:e8WAyV7lDHA.2272@.tk2msftngp13.phx.gbl...
> THANX FOR REPLYING. I am using dataset object to fetch the data and binding
> it with the data list view. In this case how I can format in the front end.
> I Know it is .Net related question but still any body knows this can answer
> for this.
> Thanx and Regards
> Babz
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eeYBLp5lDHA.2364@.TK2MSFTNGP11.phx.gbl...
> > I strongly suggest you follow Linchi's suggestion and have your client
> tool to format the data. If
> > you are saying that you are using Query Analyzer, I cannot imagine the
> format being relevant, as I
> > trust that you can understand the difference between a value and the
> presentation and format of such
> > a value. I do assume that you don't use Query Analyzer to present data to
> end users.
> >
> > Having said that, if you *really* want to do this in QA, you need to
> convert the data to a datatype
> > for which QA presents two digits to the right of the decimal point. ROUND
> does *not* change
> > datatype, it only changes the value. So, you need to use CAST for this.
> Example:
> > SELECT CAST(3.1 AS decimal(5,2))
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Babz" <notknown@.nodomain.com> wrote in message
> news:OfDZbj5lDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > > Hi
> > > I want this thing in the sql query only.
> > > In the database it is stored as 48.0000. 54.0000, 76.5786.
> > >
> > > Even after rounding the value to 2 decimals it is showing in the 4
> decimal
> > > places.
> > >
> > > I want the data in the following format
> > >
> > > 48.00
> > > 54.00
> > > 76.58
> > >
> > > Need Help
> > >
> > > Babz
> > >
> > >
> > >
> > > "Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
> > > news:Oi#15D5lDHA.2616@.TK2MSFTNGP11.phx.gbl...
> > > > Where do you want to display these numbers? What tool are you talking
> > > about?
> > > > This is an issue with the client tool you are using.
> > > >
> > > > --
> > > > Linchi Shea
> > > > linchi_shea@.NOSPAMml.com
> > > >
> > > >
> > > > "Babz" <notknown@.nodomain.com> wrote in message
> > > > news:eL0dzx4lDHA.2080@.TK2MSFTNGP10.phx.gbl...
> > > > > Hi ,
> > > > > I want to display the a numeric field column in two
> decimal
> > > > > places.
> > > > >
> > > > > 48.00
> > > > > 48.56
> > > > > But in the Qty is a Number then it is showing as 48.0. How I can
> get in
> > > > two
> > > > > decimal places
> > > > >
> > > > > Need Help
> > > > >
> > > > > Babz
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Have you ensured that your client tool actually holds the value with decimal
places?
The screen output most likely reflects the actual value held.
"Babz" <notknown@.nodomain.com> wrote in message
news:eL0dzx4lDHA.2080@.TK2MSFTNGP10.phx.gbl...
> Hi ,
> I want to display the a numeric field column in two decimal
> places.
> 48.00
> 48.56
> But in the Qty is a Number then it is showing as 48.0. How I can get in
two
> decimal places
> Need Help
> Babz
>

Format to 2 decimal places?

Hi, i need to display my data in 2 decimal places but now i'm getting results after some calculation (for eg. 2.336224). How can i round it off to 2.34?

Hi,

In the report designer, right click the cell where you want to display the above number then go to properties and then to the format tab and enter Format Code as "0.00". It should format the 2.336224 to 2.34.

Hope it helps.

Rajiv

|||hehe.. thanks so much! It's so helpful.. I came across this when i was exploring the program but didnt occur to me when i need it.. thanks!

Format string

Hii
can somebody please tell me what format string i should use to bring 3
digits after decimal in any textbox.
thanx in advanceformat(Field!Number.Value,"0.000")
"Techotsav" wrote:
> Hii
> can somebody please tell me what format string i should use to bring 3
> digits after decimal in any textbox.
> thanx in advance
>|||many many thanks (sorry didnt get ur label), i placed =format("0.000")
in formatstring textbox n bang, it works!

Format Numbers

I have a report that returns a column with number values that I want to round
to 1 decimal place. I am able to do this using the format expression #,##0.0.
However, some line items have a null value. For these line items I need to
display "-".
However, I need to do it using a format expression so that when the report
is exported to Excel the column won't be automatically converted to a string
value. Does anyone know if this is possible?Hi,
>I have a report that returns a column with number values that I want to
>round
> to 1 decimal place. I am able to do this using the format expression
> #,##0.0.
> However, some line items have a null value. For these line items I need to
> display "-".
This worked for me:
Concatenate a zero to the value...
=IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
"#,##0.0"))
HTH!
Kind regards - Fred|||Hi Fred,
Thanks for your prompt reply! I have tried your method. This works OK in
terms of the format in the report, however, when I export the report to MS
Excel 2003, the column value is automatically converted to a text string, and
therefore no number operations such as SUM or AVERAGE can be performed on the
column. Is there a format expression I can use on the column in the report
that will prevent this from occuring?
"Fred Block" wrote:
> Hi,
> >I have a report that returns a column with number values that I want to
> >round
> > to 1 decimal place. I am able to do this using the format expression
> > #,##0.0.
> > However, some line items have a null value. For these line items I need to
> > display "-".
> This worked for me:
> Concatenate a zero to the value...
> =IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
> "#,##0.0"))
> HTH!
> Kind regards - Fred
>
>|||On Feb 16, 8:03 am, TK-UK <T...@.discussions.microsoft.com> wrote:
> Hi Fred,
> Thanks for your prompt reply! I have tried your method. This works OK in
> terms of the format in the report, however, when I export the report to MS
> Excel 2003, the column value is automatically converted to a text string, and
> therefore no number operations such as SUM or AVERAGE can be performed on the
> column. Is there a format expression I can use on the column in the report
> that will prevent this from occuring?
>
> "Fred Block" wrote:
> > Hi,
> > >I have a report that returns a column with number values that I want to
> > >round
> > > to 1 decimal place. I am able to do this using the format expression
> > > #,##0.0.
> > > However, some line items have a null value. For these line items I need to
> > > display "-".
> > This worked for me:
> > Concatenate a zero to the value...
> > =IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
> > "#,##0.0"))
> > HTH!
> > Kind regards - Fred- Hide quoted text -
> - Show quoted text -
Can you take the nulls out in the database query side instead? i.e.
an expression like YourData = isnull(t1.number_column,0) in SQL Server.|||Hi...
> Can you take the nulls out in the database query side instead? i.e.
> an expression like YourData = isnull(t1.number_column,0) in SQL Server.
..and/or maybe display a "zero" instead of the "-" which is most likely why
Excel is seeing strings.
Regards - Fred|||Hi again Fred,
I can't display 0 instead of nulls because the column is displaying an
average. Therefore, to display a 0 would be incorrect, and would also affect
any aggregation computations performed on the column such as an overall
average of all line items. Do you possible know of any other ways?
Tom
"Fred Block" wrote:
> Hi...
> > Can you take the nulls out in the database query side instead? i.e.
> > an expression like YourData = isnull(t1.number_column,0) in SQL Server.
> ...and/or maybe display a "zero" instead of the "-" which is most likely why
> Excel is seeing strings.
> Regards - Fred
>
>

format number with decimals

I need to know to format a field that has decimals.

If the decimal portion is .00 then I do not want to display it.

For example:
1234.01 would display as 1234.01
1234.00 would display as 1234

Any help would be great thanks.

Dean

I found the answer:

if int({field})= {field} then
formula = totext({field},"##########")
else
formula = totext({field},"##########.##")
end ifHi,
Try this

if right(Cstr({feild}),3)=".00" then
mid(cstr({feild}),1,len(cstr({feild}))-3)
else
cstr({feild});
Madhivanan

Format number with 2 decimal places

I am having trouble formatting a DATEDIFF with 2 decimal places.
Can anyone help?
tbVisitLog
VisitID | ChildID | DateTimeIn | DateTimeOut | etc...
tbChild
ChildID | ChildLastName
When calculating duration of stay...
SELECT c.ChildFirstName + ' ' + c.ChildLastName AS ChildName,
(DATEDIFF(n,v.DateTimeIn, v.DateTimeOut) * "The Rate for the child") AS Amou
ntDue
FROM tbVisitLog v
INNER JOIN tbChild ON c.ChildID = v.ChildID
WHERE c.ChildID IN (group of children leaving)
I want to format the duration * rate to be a two-decimal number. Without for
matting I get numbers like this:
ChildName | 0.52000000000000002
ChildName | 4.0199999999999996
Thanks
Sonny
--
--Try this (untested):
SELECT c.ChildFirstName + ' ' + c.ChildLastName AS ChildName,
(cast(DATEDIFF(n,v.DateTimeIn, v.DateTimeOut) * "The Rate for the
child") as decimal(14,2)) AS AmountDue
FROM ...
ML

Format Decimal number conditionally

My 'Hour' Fields are retrieved from stored procedure always with 1 decimal
place.
(ie: 2.0 hours, 3.5 hours)
I wish to display, (ie: 2, 3.5 respectively), the first value (2.0) without
the ".0".
So far I have this solution:
=IIF(Abs(Fields!Hours.Value) < Fields!Hours.Value, Fields!Hours.Value ,
Abs(Fields!Hours.Value) )
Is there a better solution? thanks!I found an even simpler solution:
I changed the Custom Format of my table cell to:
=Format(Cdbl(Fields!Hours.Value))
"SQT" wrote:
> My 'Hour' Fields are retrieved from stored procedure always with 1 decimal
> place.
> (ie: 2.0 hours, 3.5 hours)
> I wish to display, (ie: 2, 3.5 respectively), the first value (2.0) without
> the ".0".
> So far I have this solution:
> =IIF(Abs(Fields!Hours.Value) < Fields!Hours.Value, Fields!Hours.Value ,
> Abs(Fields!Hours.Value) )
> Is there a better solution? thanks!
>|||Apology.
Do not use the Custom Format.
Change the Text Value Property to, ie: =Format(Cdbl(Fields!Hours.Value))
"SQT" wrote:
> I found an even simpler solution:
> I changed the Custom Format of my table cell to:
> =Format(Cdbl(Fields!Hours.Value))
>
> "SQT" wrote:
> >
> > My 'Hour' Fields are retrieved from stored procedure always with 1 decimal
> > place.
> > (ie: 2.0 hours, 3.5 hours)
> >
> > I wish to display, (ie: 2, 3.5 respectively), the first value (2.0) without
> > the ".0".
> > So far I have this solution:
> > =IIF(Abs(Fields!Hours.Value) < Fields!Hours.Value, Fields!Hours.Value ,
> > Abs(Fields!Hours.Value) )
> >
> > Is there a better solution? thanks!
> >

format decimal

I want to format a number, a decimal, with 2 figures afther the "," but all I get is zeroes behind it. It works if I replace "cart.GetTotalWeight(cartId)"by 12.12 for example, 12,12 does not work... It driving me crazy

dim cart = New ShoppingCartDB()
TotalweightLbl.Text = string.Format("{0:n}",cart.GetTotalWeight(cartId))

the stored procedure...

CREATE Procedure CMRC_ShoppingCartTotalweigh
(
@.CartID nvarchar (50),
@.Totalweight decimal (5,2) OUTPUT

)
AS

SELECT
@.Totalweight = SUM(Products.Unitweight * ShoppingCart.Quantity)

FROM
ShoppingCart,
Products

WHERE
ShoppingCart.CartID = @.CartID
AND
Products.ProductID = ShoppingCart.ProductID
GO

the function...

Public Function GetTotalweight (ByVal cartID As String) As Decimal

' Create Instance of Connection and Command Object
Dim myConnection As SqlConnection = New SqlConnection ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("CMRC_ShoppingCartTotalweigh", myConnection)

' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC
Dim parameterCartID As SqlParameter = New SqlParameter("@.CartID", SqlDbType.NVarChar, 50)
parameterCartID.Value = cartID
myCommand.Parameters.Add(parameterCartID)

Dim parameterTotalweight As SqlParameter = New SqlParameter("@.Totalweight", SqlDbType.decimal, )
parameterTotalweight.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterTotalweight)

' Open the connection and execute the Command
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

' Return the Total

If parameterTotalweight.Value.ToString() <> "" Then
Return parameterTotalweight.Value
Else
Return 0
End If

End Function

Dim parameterTotalweight As SqlParameter = New SqlParameter("@.Totalweight", SqlDbType.decimal, )

Tell it the precision and scale, you've chosen a decimal with a scale of 0 (The default).

Format Currency Problem - HELP!

I need to display currency fields with a dollar sign, commas and no
decimal places. I entered
=FormatCurreny(fields!nameoffield.value,0,-1,-1)in the textbox
property's custom edit expressions box.
It drops the decimal places and adds the dollar sign just fine but,
I need the commas as well. What do I do?
I would apprectiate help here.
Thanks,
GailIf your Regional Settings in Control Panel is US, then go to the Format
property of the field in the Properties sheet and type in C0. (letter "C"
followed by a zero).
If your Regional Settings is not US, then go to the Language property of the
textbox, select English (United States) from the listbox, then in the Format
property type in C0.
Alternatively, you can just use an expression: =Format(FieldName, "$#,###").
HTH
Charles Kangai, MCT, MCDBA
"Gail Beedie" wrote:
> I need to display currency fields with a dollar sign, commas and no
> decimal places. I entered
> =FormatCurreny(fields!nameoffield.value,0,-1,-1)in the textbox
> property's custom edit expressions box.
> It drops the decimal places and adds the dollar sign just fine but,
> I need the commas as well. What do I do?
>
> I would apprectiate help here.
> Thanks,
> Gail
>|||YOU ARE MY HERO!!!!
GAIL
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message news:<B6BAD0E4-6010-4560-A59D-788C94A5640E@.microsoft.com>...
> If your Regional Settings in Control Panel is US, then go to the Format
> property of the field in the Properties sheet and type in C0. (letter "C"
> followed by a zero).
> If your Regional Settings is not US, then go to the Language property of the
> textbox, select English (United States) from the listbox, then in the Format
> property type in C0.
> Alternatively, you can just use an expression: =Format(FieldName, "$#,###").
> HTH
> Charles Kangai, MCT, MCDBA
>
> "Gail Beedie" wrote:
> > I need to display currency fields with a dollar sign, commas and no
> > decimal places. I entered
> > =FormatCurreny(fields!nameoffield.value,0,-1,-1)in the textbox
> > property's custom edit expressions box.
> >
> > It drops the decimal places and adds the dollar sign just fine but,
> > I need the commas as well. What do I do?
> >
> >
> > I would apprectiate help here.
> >
> > Thanks,
> > Gail
> >|||And mine as well. I had a different issue, but this clued me into what my
problem was. Thanks
"Gail Beedie" wrote:
> YOU ARE MY HERO!!!!
> GAIL
>
> "Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message news:<B6BAD0E4-6010-4560-A59D-788C94A5640E@.microsoft.com>...
> > If your Regional Settings in Control Panel is US, then go to the Format
> > property of the field in the Properties sheet and type in C0. (letter "C"
> > followed by a zero).
> >
> > If your Regional Settings is not US, then go to the Language property of the
> > textbox, select English (United States) from the listbox, then in the Format
> > property type in C0.
> >
> > Alternatively, you can just use an expression: =Format(FieldName, "$#,###").
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> >
> > "Gail Beedie" wrote:
> >
> > > I need to display currency fields with a dollar sign, commas and no
> > > decimal places. I entered
> > > =FormatCurreny(fields!nameoffield.value,0,-1,-1)in the textbox
> > > property's custom edit expressions box.
> > >
> > > It drops the decimal places and adds the dollar sign just fine but,
> > > I need the commas as well. What do I do?
> > >
> > >
> > > I would apprectiate help here.
> > >
> > > Thanks,
> > > Gail
> > >
>

Format currency - include commas to separate per 1000 multiples?

I'm using F to format currency (UK sterling) to 2 decimal places. Is it
possible to include commas to separate 1000 multiples - e.g. 5,000,000
--
FionaDMThis article should help http://msdn2.microsoft.com/en-us/library/0c899ak8(VS.80).aspx
Put something like this in the Layout / Properties / Format box (I'm
using the US Dollar sign - you would put the UK pound sign in the
appropriate place instead):
$#,##0.00;($#,##0.00);Zero
If the value were 10000 you would see: $10,000.00
If the value were .5 you would see: $0.50
If the value were -500.05 you would see: ($500.05)
If the value were 0 you would see: Zero
- C
On Oct 3, 8:07 am, FionaDM <Fion...@.discussions.microsoft.com> wrote:
> I'm using F to format currency (UK sterling) to 2 decimal places. Is it
> possible to include commas to separate 1000 multiples - e.g. 5,000,000
> --
> FionaDM|||Perfect!
--
FionaDM
"Cindy Parker" wrote:
> This article should help http://msdn2.microsoft.com/en-us/library/0c899ak8(VS.80).aspx
> Put something like this in the Layout / Properties / Format box (I'm
> using the US Dollar sign - you would put the UK pound sign in the
> appropriate place instead):
> $#,##0.00;($#,##0.00);Zero
> If the value were 10000 you would see: $10,000.00
> If the value were .5 you would see: $0.50
> If the value were -500.05 you would see: ($500.05)
> If the value were 0 you would see: Zero
> - C
> On Oct 3, 8:07 am, FionaDM <Fion...@.discussions.microsoft.com> wrote:
> > I'm using F to format currency (UK sterling) to 2 decimal places. Is it
> > possible to include commas to separate 1000 multiples - e.g. 5,000,000
> > --
> > FionaDM
>
>

format 0.00 decimal value

Is there any option/settings in SQL server to return 0 decimal value as
"0.00"
not
".00"
I can do it by converting all results to varchar(), but is there any easy
way? may be server settings?
thanks
L
Formatting of decimal data is done by the front end, not the server.
So the problem is with Query Analyzer, or whatever tool you are using.
If it is QA there is no feature like you are asking for.
Roy Harvey
Beacon Falls, CT
On Wed, 29 Nov 2006 13:43:01 -0800, LLT
<LLT@.discussions.microsoft.com> wrote:

>Is there any option/settings in SQL server to return 0 decimal value as
>"0.00"
>not
>".00"
>I can do it by converting all results to varchar(), but is there any easy
>way? may be server settings?
>thanks
>L
|||I am using QA. The same in DTS when I extract data into txt file. Strange,
but float is returned with leading 0 as "0.00", decimal and money without as
".00". When number converted to string using cast() or str() leading 0 is
shown for all numbers.
You can run in in QA.
select 'money, with varchar cast', cast(cast (0.00 as money) as varchar(10))
select 'money, no varchar cast', cast(0.00 as money)
select 'decimal(10,2) with varchar cast', cast(cast (0.00 as decimal(10,2))
as varchar(10))
select 'decimal, no varchar cast', cast(0.00 as decimal(10,2))
select 'float, with varchar cast', cast(cast (0.00 as float) as varchar(10))
select 'float, no varchar cast', cast(0.00 as float)
"Roy Harvey" wrote:

> Formatting of decimal data is done by the front end, not the server.
> So the problem is with Query Analyzer, or whatever tool you are using.
> If it is QA there is no feature like you are asking for.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 29 Nov 2006 13:43:01 -0800, LLT
> <LLT@.discussions.microsoft.com> wrote:
>