Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Number Format in SQL SERVER and SSRS Expand / Collapse
Author
Message
Posted Wednesday, May 13, 2009 2:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 9, 2009 5:34 AM
Points: 17, 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
Post #715723
Posted Wednesday, May 13, 2009 2:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 23, 2009 9:10 AM
Points: 6, Visits: 58
Try With this:
FormatCurrency((Fields!P_OvertimeRate.Value),3)
Post #715734
Posted Wednesday, May 13, 2009 8:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 24, 2010 1:57 PM
Points: 182, 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
Post #716013
Posted Thursday, May 14, 2009 1:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 9, 2009 5:34 AM
Points: 17, 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.


Post #716684
Posted Friday, May 15, 2009 7:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 4, 2010 10:17 AM
Points: 191, 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
Post #717913
Posted Friday, May 15, 2009 9:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 11:56 AM
Points: 317, Visits: 1,529
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



Post #717992
Posted Friday, May 15, 2009 10:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 4, 2010 10:17 AM
Points: 191, 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
Post #718077
Posted Friday, May 15, 2009 10:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 11:56 AM
Points: 317, Visits: 1,529
Well, shucks. Looks like I tested for situations of 182 vs 182.09; didn't test for 182.99. Good catch.


Post #718102
Posted Monday, May 18, 2009 1:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 9, 2009 5:34 AM
Points: 17, 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
Post #718839
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse