SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Number Format in SQL SERVER and SSRS


Number Format in SQL SERVER and SSRS

Author
Message
vikas_yadav24
vikas_yadav24
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 51
I Want to diplay float value in following format
can any bodt tell me function for that

Actaul val Val to display
188 --> 188
188.02 --> 188.0
188.25 --> 188.3
188.96 --> 189

Please tell me any function or custom format for this
sreshyam
sreshyam
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 58
Try With this:
FormatCurrency((Fields!P_OvertimeRate.Value),3)
Eric Peterson
Eric Peterson
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 65
=Format(Fields!YourFieldName.value, "##,##0.00")

this works better for float numbers, as you can set the decimals ny changing the format string above
vikas_yadav24
vikas_yadav24
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 51
Thanks all for replying. But both answers are not satisfying my requirement.
In Case of currency, this number is simple number, not a currency so I dodn't need any $ sign before these numbers.

Other thing, IN case of simple integer like 182, I want to display 182 only
not 182.0
even in case of 182.96 I want to display 183 not 183.0

while in case of 182.02 it should be 182.0
and 182.36 it should be 182.4

I think now I made it clear.

Thanks again for your help.
Siva Gurusamy
Siva Gurusamy
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 386
=Math.Round(188.36, 1, MidpointRounding.AwayFromZero)
will work for all but this: while in case of 182.02 it should be 182.0

View Siva Gurusamy's profile on LinkedIn

"Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation
peterzeke
peterzeke
Say Hey Kid
Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)

Group: General Forum Members
Points: 666 Visits: 1766
Try this:

=iif(
(YourField.Value/cint(YourField.Value))=1
,formatnumber(YourField.Value,0)
,formatnumber(YourField.Value,1)
)

Just substitute "YourField" with the field your need to evaluate.
--pete



Siva Gurusamy
Siva Gurusamy
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 386
Peter, your exp did not work for this req: even in case of 182.96 I want to display 183 not 183.0

Modified a bit a to arrive at:

=iif(int32.tryparse(182.96,182.96),182.96,iif(round(182.96) > 182.96,round(182.96),format(Math.Round(182.96, 1, MidpointRounding.AwayFromZero),"#.0#")))

Check this out Yadav

View Siva Gurusamy's profile on LinkedIn

"Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation
peterzeke
peterzeke
Say Hey Kid
Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)

Group: General Forum Members
Points: 666 Visits: 1766
Well, shucks. Looks like I tested for situations of 182 vs 182.09; didn't test for 182.99. Good catch.



vikas_yadav24
vikas_yadav24
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 51
Thanks everybody for replying.

I was looking for some function, which solve my proble.
But I didn't find any.
Now I made my own case

CASE WHEN CHARINDEX('.',MYVALUE) = 0 THEN CAST(MYVALUE AS VARCHAR)
WHEN CHARINDEX('.9',MYVALUE) = 0 THEN CAST(CONVERT(DECIMAL(6,1),MYVALUE) AS VARCHAR(7))
ELSE REPLACE(CAST(CONVERT(DECIMAL(6,1),MYVALUE) AS VARCHAR(7)),'.0','')
END


Thanks again to all
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search