Displaying Small Money

Hi,
How do I remove the decimal places from this to show just whole pounds
?
SELECT '=A3' + convert(varchar(30),PotentialRevenue,3) as [Potential
Revenue]
What is the purpose of the small money data type ? - its seems to have
no features for storing/displaying financial data that cant be done
with other data types.> How do I remove the decimal places from this to show just whole pounds?
The formatting of numeric values is controlled by your client
application, not by SQL Server and that's how it should be. ROUND the
value on the server if you need to but do presentation client-side.

> What is the purpose of the small money data type ?
Good question. In some cases the money types may save you 1 byte over a
DECIMAL column. However, given the rounding errors caused when you
divide or multiply the money types I would always avoid them unless
compelled to use them. I can't think of a good reason to use MONEY or
SMALLMONEY.
David Portas
SQL Server MVP
--|||> DECIMAL column. However, given the rounding errors caused when you
> divide or multiply the money types I would always avoid them unless
> compelled to use them. I can't think of a good reason to use MONEY or
> SMALLMONEY.
Nor can I.
For the OP's benefit: http://www.aspfaq.com/2503

Displaying seconds value as [mm]:ss

Hello
I am returning a int value from a database which is for an elasped
time in seconds. I want to convert this to mm:ss, however I dont want
it roll over to hours or days.
I trying something along the lines of:
=String.Format("{0:0:mm:ss}",CDate("0:0:0").AddSeconds(Sum(Fields!Airtime.Value)))
However this still allows values to go to hours/days, just doesn't
display them
So for example a value returned from the database of 19257 (seconds)
should be displayed as 320:57 ([mm]:ss)
This is achived in excel by
19257 / 24 = 802.375 / 60 = 13.37291667 /60 = 0.222881944
Then change the format on the cell to [mm]:ss
Then 320:57 is displayed.
Any ideas would be great
Thanks
DavidYou could create custom function, something like this:
Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = seconds - ss * 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
and then call it from textbox expression:
=Code.Seconds2mmss(Sum(Fields!Airtime.Value))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <david.glasgow@.unison.co.nz> wrote in message
news:9f43b964.0408112015.79b5c9b9@.posting.google.com...
> Hello
> I am returning a int value from a database which is for an elasped
> time in seconds. I want to convert this to mm:ss, however I dont want
> it roll over to hours or days.
> I trying something along the lines of:
> =String.Format("{0:0:mm:ss}",CDate("0:0:0").AddSeconds(Sum(Fields!Airtime.Value)))
> However this still allows values to go to hours/days, just doesn't
> display them
> So for example a value returned from the database of 19257 (seconds)
> should be displayed as 320:57 ([mm]:ss)
> This is achived in excel by
> 19257 / 24 = 802.375 / 60 = 13.37291667 /60 = 0.222881944
> Then change the format on the cell to [mm]:ss
> Then 320:57 is displayed.
> Any ideas would be great
> Thanks
> David|||Thanks for that, the final code that returned the result was:
Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = (seconds - ss) / 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
Thanks again for your help

Displaying Encoded Characters

some special characters are saved in an encoded form. However reporting
services does not convert it back to the regular form when it retreives it
from the database, instead it would output get wierd characters.the code for keystrokes ALT 250 and others are whats being outputted not the
correct symbol.
"Marvin" wrote:
> some special characters are saved in an encoded form. However reporting
> services does not convert it back to the regular form when it retreives it
> from the database, instead it would output get wierd characters.

Display ONLY Month, year from SQL2k

Tried this:

SELECT CONVERT(varchar,fieldMonthYear,107) 'Month in Question' FROM ....

That returns: Apr 01, 2006
What I need is this: April 2006 or even Apr 2006. But no date for the day.

Is there a way I can trim the center 4 characters of this now converted varchar? This is in a datalist, btw. Thanks!

bs.

SELECT Left(CONVERT(varchar,getdate(),107), 4) + RIGHT(CONVERT(varchar,getdate(),107), 4) AS 'Month in Question'|||

Thank you.

while you gave an acceptable answer, I found another way - I'll still mark your answer as right because it answered my question originally.

This is what I used to get it to work:

SELECT CONVERT(VARCHAR(10), DATENAME(MM,fieldMonthYear)) + ', ' + CONVERT(VARCHAR(4), Year(fieldMonthYear)) AS 'Month in Question' FROM tblTableName

God bless...
iSheahan

|||

There is a better way to format datetime from client side.

<asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource3">
<ItemTemplate>

LastActivityDate:
<asp:Label ID="DateLabel" runat="server" Text=' <%# DataBinder.Eval(Container.DataItem, "fieldMonthYear","{0:MMM yyyy}") %>'> </asp:Label>

</ItemTemplate>

</asp:DataList>

Or simply: <asp:Label ID="Label1" runat="server" Text=' <%# Eval("fieldMonthYear","{0:MMM yyyy}") %>'> </asp:Label>

Enjoy.

display FLOAT datatype with commas?

Is it possible to display a FLOAT datatype with all its commas every
three digits? I can't figure out how to use CAST or CONVERT to do this.
(I know this kind of thing is usually done on the client side, but
that's not possible right now.) Thanks much.here you go
DECLARE @.v float
SELECT @.v = 1322323.6666
SELECT CONVERT(VARCHAR,convert(money,@.v),1)
http://sqlservercode.blogspot.com/|||Why is this a float?
Anyway, see http://tinyurl.com/zlbmw
This is not pretty.
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1e7917a698fb998c989921@.msnews.microsoft.com...
> Is it possible to display a FLOAT datatype with all its commas every
> three digits? I can't figure out how to use CAST or CONVERT to do this.
> (I know this kind of thing is usually done on the client side, but
> that's not possible right now.) Thanks much.|||FANTASTIC. Thank you.
Is there a way to right-justify the output? (I know we have character
data now, so it may not be possible?) Thanks.
In article <1141853921.455400.117020@.j52g2000cwj.googlegroups.com>,
denis.gobo@.gmail.com says...
> here you go
> DECLARE @.v float
> SELECT @.v = 1322323.6666
> SELECT CONVERT(VARCHAR,convert(money,@.v),1)
> http://sqlservercode.blogspot.com/
>|||What if it's a bigger FLOAT value than MONEY can support?
DECLARE @.v float
SELECT @.v = 1321324441322323.6666
Also, you should always specify the size of your VARCHAR parameters, IMHO.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141853921.455400.117020@.j52g2000cwj.googlegroups.com...
> here you go
> DECLARE @.v float
> SELECT @.v = 1322323.6666
> SELECT CONVERT(VARCHAR,convert(money,@.v),1)
> http://sqlservercode.blogspot.com/
>|||> Is there a way to right-justify the output?
Umm, sure, you could say RIGHT(SPACE(30)+...stuff..., 30)
But what application is consuming this result that can't right-justify ?
Are you going to ask for a way to embed <img> tags next? :-)
T-SQL is not meant to be a presentation language.|||What is the basic principle of a tiered architecture? We do display
formatting in the front end and never in the database. Since FLOAT
can come back as exponential notation, you really don't want to do it
in SQL.|||Brilliant. Thank you VERY much. Much appreciated. I know T-SQL is not
meant to be a presentation language, but for a very quick one-time
temporary project I am forced to use it as such. This is perfect.
Thank you.
In article <OPUigrvQGHA.4956@.TK2MSFTNGP09.phx.gbl>,
ten.xoc@.dnartreb.noraa says...
> Umm, sure, you could say RIGHT(SPACE(30)+...stuff..., 30)
> But what application is consuming this result that can't right-justify ?
> Are you going to ask for a way to embed <img> tags next? :-)
> T-SQL is not meant to be a presentation language.
>
>