Formatting Query Data

When I run queries against tables in MSSQL2000 it returns dates with a time component and has way too many zeros after the decimal point.

Is there any functions or something I can put in the query so my data comes back with only a date for the date and only two decimal points for the numbers?You are generally best off leaving the formatting to the code that receives the data from the database. But you can use the Convert function to format a date and the round function for numeric values.

SELECT Convert(varchar,DateField,101),Round(NumericField,2)
FROM YourTable

However, Round may not work quite as you expect. It will round the value but not necessarily format it for display as you might expect. Other date formats are available, check Sql Server Books OnLine under the convert function for the options.|||I agree with McMurdo, however, that the best place to handle this is outside of the database, in your code that is displaying the data.|||I agree, this is what I wanted - the ability to have the data formatted correctly from SQL. I tried for ages to find a way to do that on the basis of what I new about MySQL (which has a lot of functions for this).

But I'll look into it, as ideally I want my data coming in right from the SQL Query, rather than .Net doing it. Indeed, there is another thread in another forum arguing that as people kept giving me the whole Eval solution.|||Can you strip trailing spaces as well - I have a CUSTNMBR that is 5 characters long put it pads it with four spaces to to make it 9 (the length of the field).

I'll investigate the day, so I may have the answer this morning (UK Time).|||Use RTRIM or LTRIM to strip spaces.sql

Format of zeros

How would I clear up all the zeros in a report to either blanks or dashes?

Example:

With a standard format numbers:

Col A | Col B

--

1.00 | 0.00

I want to clean it up like in Excel's Cell format called "Accounting"?

Col A | Col B

--

1.00 | -

This makes it easy to read

You can use expression to show "-" conditionally, i.e.

=iif(Fields!ColB.Value = 0,"-",Fields!ColB.Value)

|||

You can use custom numeric format strings as well, for example:

=Fields!ColB.Value.ToString("#.00;(#.00);-")

Format of zeros

How would I clear up all the zeros in a report to either blanks or dashes?

Example:

With a standard format numbers:

Col A | Col B

--

1.00 | 0.00

I want to clean it up like in Excel's Cell format called "Accounting"?

Col A | Col B

--

1.00 | -

This makes it easy to read

You can use expression to show "-" conditionally, i.e.

=iif(Fields!ColB.Value = 0,"-",Fields!ColB.Value)

|||

You can use custom numeric format strings as well, for example:

=Fields!ColB.Value.ToString("#.00;(#.00);-")

Format Numbers

How do I add leading ZEROs in front of an INT variable in TSQL?
From
@.Var = 123
to
@.Var = 000123
If you have leading zeros, then it is no longer an INT and it is now a
string.
DECLARE @.var INT;
SET @.var = 123;
SELECT RIGHT('000000' + RTRIM(@.var), 6);
"TBoon" <TBoon@.discussions.microsoft.com> wrote in message
news:4D0B6BFC-3C2F-4086-B250-47574EA91E25@.microsoft.com...
> How do I add leading ZEROs in front of an INT variable in TSQL?
> From
> @.Var = 123
> to
> @.Var = 000123

Format Numbers

How do I add leading ZEROs in front of an INT variable in TSQL?
From
@.Var = 123
to
@.Var = 000123If you have leading zeros, then it is no longer an INT and it is now a
string.
DECLARE @.var INT;
SET @.var = 123;
SELECT RIGHT('000000' + RTRIM(@.var), 6);
"TBoon" <TBoon@.discussions.microsoft.com> wrote in message
news:4D0B6BFC-3C2F-4086-B250-47574EA91E25@.microsoft.com...
> How do I add leading ZEROs in front of an INT variable in TSQL?
> From
> @.Var = 123
> to
> @.Var = 000123

Format Numbers

How do I add leading ZEROs in front of an INT variable in TSQL?
From
@.Var = 123
to
@.Var = 000123If you have leading zeros, then it is no longer an INT and it is now a
string.
DECLARE @.var INT;
SET @.var = 123;
SELECT RIGHT('000000' + RTRIM(@.var), 6);
"TBoon" <TBoon@.discussions.microsoft.com> wrote in message
news:4D0B6BFC-3C2F-4086-B250-47574EA91E25@.microsoft.com...
> How do I add leading ZEROs in front of an INT variable in TSQL?
> From
> @.Var = 123
> to
> @.Var = 000123

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.