formating decimals

Hi, I got money types coming out of the db.
I use #,##0.## all is swell 14.1400 comes out as 14.14,
14.0000 comes out as 14
but then I get 0 or 0.00, it comes out 0.00
I want it to be just 0
ThanksI would compare the value with what you consider a small enough value to be
taken as zero and use the appropriate formatting
iif(abs(dbvalue) < 0.0001, "0", "#,##0.##")
Andrei.
"yurps" <yurps@.yahoo.co.uk> wrote in message
news:1115831567.239111.316890@.o13g2000cwo.googlegroups.com...
> Hi, I got money types coming out of the db.
> I use #,##0.## all is swell 14.1400 comes out as 14.14,
> 14.0000 comes out as 14
> but then I get 0 or 0.00, it comes out 0.00
> I want it to be just 0
> Thanks
>

Format of DateTime types in Select vs Open Table

From within the SQL Server Management Studio, if I select a table and displa
y
it (right click Open Table), columns of type DateTime are displayed with the
mm/dd/yyyy hh:mm:ss format. However, if I SELECT * FROM the same table from
a
query in the Query Analyzer (or New Query in 2005), the same column is
displayed with a yyyy-mm-dd hh:mm:ss format.
Why is there a difference in display format between opening the table in the
Management Studio and SELECT'ing in a query?
Short of using a CONVERT in the SELECT, is there a way of controlling the
display format of DateTime types in both scenarios?
Michael
--
Michael Hocksteinmichael (howlinghound@.nospam.nospam) writes:
> From within the SQL Server Management Studio, if I select a table and
> display it (right click Open Table), columns of type DateTime are
> displayed with the mm/dd/yyyy hh:mm:ss format. However, if I SELECT *
> FROM the same table from a query in the Query Analyzer (or New Query in
> 2005), the same column is displayed with a yyyy-mm-dd hh:mm:ss format.
> Why is there a difference in display format between opening the table in
> the Management Studio and SELECT'ing in a query?
Open Table respects the regional settings, while by default Query Analyzer
does not. You can change this for QA by going into Tools->Options->
Connections and check "Use regional settings...". There does not seem to be
any similar option for Management Studio. but query results always
apparently uses ISO format. (I can't really tell from here, because my
regional settings are the ISO format.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you!
Michael Hockstein
"Erland Sommarskog" wrote:

> michael (howlinghound@.nospam.nospam) writes:
> Open Table respects the regional settings, while by default Query Analyzer
> does not. You can change this for QA by going into Tools->Options->
> Connections and check "Use regional settings...". There does not seem to b
e
> any similar option for Management Studio. but query results always
> apparently uses ISO format. (I can't really tell from here, because my
> regional settings are the ISO format.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Format date with leading zeros

Right now we're displaying dates like this: 2/6/2006
We'd like to display them like this: 02/06/2006
Currently we have two types of dates we're dealing with. In the first
case
we have a datetime and we're doing this:
=FormatDateTime(Code.GetStartDate(),vbShortDate)
in the second we just have a date, and we set the format code to 'd'
by selecting one of three date formats that are offered when we hit the
... button
on the Format tab.
Anyway, in each case we want the leading zeros.
TIA,
JimLet me refine that.
I've solved this to a point by using mm/dd/yyyy in the format. But
it's entirely possible
that this will be used where we would want dd/mm/yyyy.
So I would want a format that will adapt to regional settings, but will
also show leading zeros.
But only if it's simple ;)|||Hi,
If I have understood your problem correctly, the "d" format code will do
this, according to the language property of the report.
E.g. if the date '8 Jan 2006 23:15:58' would display as '08/01/2006' if
format was set to "d" and Language to 'English(United Kingdom).
If Language was set to 'English(United States), it would display as
'01/08/2006'.
If Language was set to '=User.Language', it would display in whatever format
was set on the client machine.
I hope this helps.
Ed Allison
<jhcorey@.yahoo.com> wrote in message
news:1140125197.767867.274710@.g43g2000cwa.googlegroups.com...
> Let me refine that.
> I've solved this to a point by using mm/dd/yyyy in the format. But
> it's entirely possible
> that this will be used where we would want dd/mm/yyyy.
> So I would want a format that will adapt to regional settings, but will
> also show leading zeros.
> But only if it's simple ;)
>|||I probably should have added that that is what we were using
originally, and while it no doubt handles the different regional
formats, it would not display the leading zeros.|||Really? It does for me. Sorry not to have been of more help.
<jhcorey@.yahoo.com> wrote in message
news:1140194093.511041.100970@.z14g2000cwz.googlegroups.com...
>I probably should have added that that is what we were using
> originally, and while it no doubt handles the different regional
> formats, it would not display the leading zeros.
>|||Hmm...
This made me think that it has to do with the regional settings on my
machine.
So I went in there and changed the short date format to mm/dd/yyyy.
But that didn't do it. I'm running everything local (I think). But I
think I'm on a track.