Number Format in SQL SERVER and SSRS

Question

Post reply

Number Format in SQL SERVER and SSRS

vikas_yadav24

SSC Eights!

Points: 851
More actions
May 13, 2009 at 2:35 am

#204289

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sreshyam Old Hand Points: 358 More actions · Answer 1

Try With this:

FormatCurrency((Fields!P_OvertimeRate.Value),3)

Eric Peterson Hall of Fame Points: 3192 More actions · Answer 2

=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 SSC Eights! Points: 851 More actions · Answer 3

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 SSCrazy Points: 2711 More actions · Answer 4

=Math.Round(188.36, 1, MidpointRounding.AwayFromZero)

will work for all but this: while in case of 182.02 it should be 182.0

"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 SSCrazy Eights Points: 9003 More actions · Answer 5

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 SSCrazy Points: 2711 More actions · Answer 6

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

"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 SSCrazy Eights Points: 9003 More actions · Answer 7

Well, shucks. Looks like I tested for situations of 182 vs 182.09; didn't test for 182.99. Good catch.

vikas_yadav24 SSC Eights! Points: 851 More actions · Answer 8

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