Formatting the numerical output

Hi All,

I need to control the output format of a request on "int" columns. For instance, if the "select" returns me a "0" (zero), I would like it to be return with 4 digit = "0000" because this results is then append to some other to built a phone-like number : +33 2121-0000-1544.
In that case, "0" is not equal to "0000"

My ASP request is in attach.

THX.

Fabrice.I found a solution ... Thank's anyway ;)

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

Formating

I have a field which returns a decimal (3.0000) i only want the whole
value(3) not the parts (.0000) to be displayed in the report.
when i right click on the textbox and select propeties then under the
'format' section i click on 'number' and select '1234' hit 'ok' and preview
the report it still displays 3.000.
how can i display just the 3(whole value as opposed to the part)?
Tia,
VinnySet the format to "F0"
"Vinny Vinn" wrote:
> I have a field which returns a decimal (3.0000) i only want the whole
> value(3) not the parts (.0000) to be displayed in the report.
> when i right click on the textbox and select propeties then under the
> 'format' section i click on 'number' and select '1234' hit 'ok' and preview
> the report it still displays 3.000.
> how can i display just the 3(whole value as opposed to the part)?
> Tia,
> Vinny|||Thank You,it worked!!
"David Siebert" wrote:
> Set the format to "F0"
> "Vinny Vinn" wrote:
> > I have a field which returns a decimal (3.0000) i only want the whole
> > value(3) not the parts (.0000) to be displayed in the report.
> > when i right click on the textbox and select propeties then under the
> > 'format' section i click on 'number' and select '1234' hit 'ok' and preview
> > the report it still displays 3.000.
> > how can i display just the 3(whole value as opposed to the part)?
> >
> > Tia,
> > Vinny

FORMAT_STRING "Currency" returns different currencies

hi!

i'm just getting to know SSAS. i created a standard measure and a calculated measure. the standard measure returns my values in euros, whereas the calculated measure displays them in swiss francs. the analysis server language (which one can set in sql server management studio) is German (Germany). my operating system language is German (Switzerland) though. seems as if calculated measures would read their standard value out of the system language, instead of the SSAS language - is that true? and how can it be fixed?

thank you,

Nico

Please check the following blog - http://www.sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx it should help you to set up the formatting of currencies the way you want it.

Format Numbers

I have a report that returns a column with number values that I want to round
to 1 decimal place. I am able to do this using the format expression #,##0.0.
However, some line items have a null value. For these line items I need to
display "-".
However, I need to do it using a format expression so that when the report
is exported to Excel the column won't be automatically converted to a string
value. Does anyone know if this is possible?Hi,
>I have a report that returns a column with number values that I want to
>round
> to 1 decimal place. I am able to do this using the format expression
> #,##0.0.
> However, some line items have a null value. For these line items I need to
> display "-".
This worked for me:
Concatenate a zero to the value...
=IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
"#,##0.0"))
HTH!
Kind regards - Fred|||Hi Fred,
Thanks for your prompt reply! I have tried your method. This works OK in
terms of the format in the report, however, when I export the report to MS
Excel 2003, the column value is automatically converted to a text string, and
therefore no number operations such as SUM or AVERAGE can be performed on the
column. Is there a format expression I can use on the column in the report
that will prevent this from occuring?
"Fred Block" wrote:
> Hi,
> >I have a report that returns a column with number values that I want to
> >round
> > to 1 decimal place. I am able to do this using the format expression
> > #,##0.0.
> > However, some line items have a null value. For these line items I need to
> > display "-".
> This worked for me:
> Concatenate a zero to the value...
> =IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
> "#,##0.0"))
> HTH!
> Kind regards - Fred
>
>|||On Feb 16, 8:03 am, TK-UK <T...@.discussions.microsoft.com> wrote:
> Hi Fred,
> Thanks for your prompt reply! I have tried your method. This works OK in
> terms of the format in the report, however, when I export the report to MS
> Excel 2003, the column value is automatically converted to a text string, and
> therefore no number operations such as SUM or AVERAGE can be performed on the
> column. Is there a format expression I can use on the column in the report
> that will prevent this from occuring?
>
> "Fred Block" wrote:
> > Hi,
> > >I have a report that returns a column with number values that I want to
> > >round
> > > to 1 decimal place. I am able to do this using the format expression
> > > #,##0.0.
> > > However, some line items have a null value. For these line items I need to
> > > display "-".
> > This worked for me:
> > Concatenate a zero to the value...
> > =IIF(Fields!YourData.Value + 0 = 0, "-", Format(Fields!YourData.Value,
> > "#,##0.0"))
> > HTH!
> > Kind regards - Fred- Hide quoted text -
> - Show quoted text -
Can you take the nulls out in the database query side instead? i.e.
an expression like YourData = isnull(t1.number_column,0) in SQL Server.|||Hi...
> Can you take the nulls out in the database query side instead? i.e.
> an expression like YourData = isnull(t1.number_column,0) in SQL Server.
..and/or maybe display a "zero" instead of the "-" which is most likely why
Excel is seeing strings.
Regards - Fred|||Hi again Fred,
I can't display 0 instead of nulls because the column is displaying an
average. Therefore, to display a 0 would be incorrect, and would also affect
any aggregation computations performed on the column such as an overall
average of all line items. Do you possible know of any other ways?
Tom
"Fred Block" wrote:
> Hi...
> > Can you take the nulls out in the database query side instead? i.e.
> > an expression like YourData = isnull(t1.number_column,0) in SQL Server.
> ...and/or maybe display a "zero" instead of the "-" which is most likely why
> Excel is seeing strings.
> Regards - Fred
>
>

Format issues - two-column report

I have a report the gets the following data for each member in a report
table format, this query takes parameters and returns about 20 records
and each has the same information shown below.
Name
company
title
address
phone
email
effective date
However, instead of this data "snaking" to the next column it prints
our five pages of this data with one column. I selected "2 columns"
for the body, and increased the body width, but only one column shows
up in the preview mode and afer I deploy the report. What can I do to
make this report have two columns and have a max of four rows per page,
with two records in each column?Try exporting to a paginated output format like Acrobat or TIFF and see if
it snakes then. It will only print one column in HTML.
--
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.amazon.com/exec/obidos/tg/detail/-/1590594231/102-0081700-1383300
http://www.apress.com/book/bookDisplay.html?bID=365
"newbie_dba" <gnice4ever@.yahoo.com> wrote in message
news:1102956134.674586.303090@.z14g2000cwz.googlegroups.com...
>I have a report the gets the following data for each member in a report
> table format, this query takes parameters and returns about 20 records
> and each has the same information shown below.
>
> Name
> company
> title
> address
> phone
> email
> effective date
>
> However, instead of this data "snaking" to the next column it prints
> our five pages of this data with one column. I selected "2 columns"
> for the body, and increased the body width, but only one column shows
> up in the preview mode and afer I deploy the report. What can I do to
> make this report have two columns and have a max of four rows per page,
> with two records in each column?
>|||"Try exporting to a paginated output format like Acrobat or TIFF and
see if
it snakes then. It will only print one column in HTML."
Still doesn't work any other suggestions. Does there have to be a
group by clause in the report?|||Using the list layout object and exporting to Acrobat or TIFF does show
multi-column reports.

FORMAT ISSUE... what to do?

Hey,
I have a little format issue with Excel 2003:
I am working in reporting services - creating a report. The SQL store
procedure returns a date with the following format:
MM/DD/YYYY HH:MM PM/AM
In my report on that perticular text field I have the following format:
dd/mm/yyyy HH:MM:SS
Because I want it in military time.
It displays fine in the report but when I open it in Excel it brings up
the following ERROR:
"File Error. Some number formats may have been lost"
And it shows the date columns the following way:
"38692.46597"
The document is NOT huge and does't contain more than 2000 rows.
Any ideas on how to solve this?Alright solved this:
Changed the format
"dd/mm/yyyy HH:MM:SS"
to
"dd/mm/yyyy HH:MM:ss"
seems that did it ... weird
regards,
Sorcerdon

Format date in a Stored Procedure

Dear friends,

I have a stored procedure that returns some fiels. One of the fields is a datetime type.

The field return in the follow format : 2006-11-13 0:00:00

How can I return only 2006-11-13? How can I use the format function?

regards!!!

declare @.someDate datetime
set @.someDate = getdate()
select cast(datepart(yyyy, @.someDate) as varchar) + '-' + cast(datepart(mm, @.someDate) as varchar) + '-' + cast(datepart(dd, @.someDate) as varchar)

result: 2006-11-13

|||

Or...

Select Convert(varchar(10), GetDate(), 120)

Lookup the convert function in Books On Line for more formats.

|||

Here is another method:

select DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))

This keeps the datatype as a datetime while removing the date from the string. It is best practice to format the data in the UI.

|||

Dear friens,

First, let me thank for all your support.

And the last question about this problem, How can get the system current time in format hh:mm? (ex: 12:30)

Thanks

|||You should not care about formatting the date on the server this is a thing for the presentation layer.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||ok, but how I return the time value of the system?|||Do you mean at the presentation layer ? That depends on your used coding language, with .NET you will date various options on the Date type.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

For example: I want to create the follow stored procedure:

CREATE PROCEDURE TEST

@.ID INT

AS

UPDATE TABLE1 SET MyFieldTime=@.MySystemTime WHERE MyFieldID=@.ID

Understood?

I want to save in my database th system time...

Thanks!!

|||Depending on which datatype you use in the column you can′t separate the date and the time. Datetime is a combined type storing date as well as time. Is it against any rules storing the date additionally ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I save the time field in teh database is a nchar(5) as for example: 12:30, 22:30, 08h00

Thanks!!

Format a datetime columns output?

Hi All,
Currently the query returns 2006-03-27 00:00:00, can I make it output
03/27/2006, I want to truncate the time, and replace the hyphens with
forward slashes. Any ideas?

Thanks In Advance,
~CKCK wrote:
> Hi All,
> Currently the query returns 2006-03-27 00:00:00, can I make it output
> 03/27/2006, I want to truncate the time, and replace the hyphens with
> forward slashes. Any ideas?
> Thanks In Advance,
> ~CK

SQL Server has no control over how dates are displayed. You need to fix
the formatting in your client application or development environment.
It's hard to help you because you haven't told us what your client
environment is.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||CK (c_kettenbach@.hotmail.com) writes:
> Currently the query returns 2006-03-27 00:00:00, can I make it output
> 03/27/2006, I want to truncate the time, and replace the hyphens with
> forward slashes. Any ideas?

To add to David's reply, if you are using Query Analyzer for output,
you can change how dates are formatted under Tools->Options->Connections.
Check "Use Regional Settings...".

You can also do it in an SQL query, look up CASE and CONVERT in Books
Online. But this is a really poor alternative. Date formatting should
be handled by the client, so that the user's regional settings can be
respected.

--
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|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97DBEF917A66FYazorman@.127.0.0.1...
> CK (c_kettenbach@.hotmail.com) writes:
>> Currently the query returns 2006-03-27 00:00:00, can I make it output
>> 03/27/2006, I want to truncate the time, and replace the hyphens with
>> forward slashes. Any ideas?
> To add to David's reply, if you are using Query Analyzer for output,
> you can change how dates are formatted under Tools->Options->Connections.
> Check "Use Regional Settings...".
> You can also do it in an SQL query, look up CASE and CONVERT in Books
> Online. But this is a really poor alternative. Date formatting should
> be handled by the client, so that the user's regional settings can be
> respected.

I used , Convert(varchar(25), L.FromDate, 101) AS FromDate in the View and
then CAST(FromDate AS DateTime)>= '03/27/2004' in the query. This is an
internal application but I see why it should be done in code. Thanks!|||Erland Sommarskog wrote:
> CK (c_kettenbach@.hotmail.com) writes:
> > Currently the query returns 2006-03-27 00:00:00, can I make it output
> > 03/27/2006, I want to truncate the time, and replace the hyphens with
> > forward slashes. Any ideas?
> To add to David's reply, if you are using Query Analyzer for output,
> you can change how dates are formatted under Tools->Options->Connections.
> Check "Use Regional Settings...".
> You can also do it in an SQL query, look up CASE and CONVERT in Books
> Online. But this is a really poor alternative. Date formatting should
> be handled by the client, so that the user's regional settings can be
> respected.

You can't actually format dates in a query at all. You can output a
string that looks like a date. I know you know this Erland, but the
difference is worth stating given that the application probably treats
dates and strings very differently.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> You can't actually format dates in a query at all. You can output a
> string that looks like a date. I know you know this Erland, but the
> difference is worth stating given that the application probably treats
> dates and strings very differently.

Well, it depends on what the application will use the output for. If it's
going into a report, it's just another string for the application. If the
application is going do some date logic with it, the result can only be
confusion.

--
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|||CK (c_kettenbach@.hotmail.com) writes:
> I used , Convert(varchar(25), L.FromDate, 101) AS FromDate in the View
> and then CAST(FromDate AS DateTime)>= '03/27/2004' in the query. This
> is an internal application but I see why it should be done in code.

This looks just wrong to me.

If you have a view, and need a condition on it with >=, why one Earth would
you could convert the date to a string? And why would you convert it to a
string that only works sometimes? When working with dates in literals in
SQL Server you should use the format YYYYMMDD, because this format is always
interpreted the same. Most other format depends on language and date format
settings.

If you are passing dates from the application as parameters, you should use
parameterised statements and pass datetime values as such not as strings.

--
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