Formatting text

I need to format a text in a text box to show a text field truncated.
In other terms, I must show only 40 characters of a very long string.
Do I use the statement "format"?
How can I solve this issue?
Many thanksI would do it in the SQL statement. As in left(fieldname, 40)
Richard
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:2550C968-0324-4FC8-A50D-67645F99C4BD@.microsoft.com...
>I need to format a text in a text box to show a text field truncated.
> In other terms, I must show only 40 characters of a very long string.
> Do I use the statement "format"?
> How can I solve this issue?
> Many thanks|||Or in the report itself:
Field => expression => LEFT(strVariable, 40)|||OK! Many thanks
"Vyv" wrote:
> Or in the report itself:
> Field => expression => LEFT(strVariable, 40)
>

Formatting in Exported Reports

Hi. I have a matrix report that has very dynamic data attached to it,
specifically text that can vary in length from 5 characters to 100 or more.
I found the best solution for rendering was to keep the actual matrix row and
column widths to a minimum and let the matrix grow as required.
The problem I have run into is, when you export to Excel or PDF, it appears
the export uses these minimum column/row widths, and the report does not look
good and requires alot of formatting by hand (including another problem with
column headings I posted).
So my question is, is there any way to work around this or a best practices?
Any help would definitely be appreciated.Actually, this problem is only in Excel.
"comet61" wrote:
> Hi. I have a matrix report that has very dynamic data attached to it,
> specifically text that can vary in length from 5 characters to 100 or more.
> I found the best solution for rendering was to keep the actual matrix row and
> column widths to a minimum and let the matrix grow as required.
> The problem I have run into is, when you export to Excel or PDF, it appears
> the export uses these minimum column/row widths, and the report does not look
> good and requires alot of formatting by hand (including another problem with
> column headings I posted).
> So my question is, is there any way to work around this or a best practices?
> Any help would definitely be appreciated.sql

Formatting a Select Statement

Hi everyone!
I am looking for a function similar to the Left(), Right(), and
Replace() functions to insert characters into a returned list of my
date field. The date currently looks like this "06202006" the function
I am looking for would return "06/20/2006" something like this if
Insert() was an actual function:
SELECT DISTINCT insert("/", right(insert("/",left(date, 2)), 2)
FROM tblLogs;
Please let me know if you have any experience with this.Perhaps you are looking for STUFF:
SELECT STUFF(STUFF([date], 5, 0, '/'), 3, 0, '/')
FROM tblLogs;
However, data formatting is better done in the presentation layer rather
than the database code.
Hope this helps.
Dan Guzman
SQL Server MVP
"EESP" <johnson4@.wwu.edu> wrote in message
news:1150932662.524736.128910@.b68g2000cwa.googlegroups.com...
> Hi everyone!
> I am looking for a function similar to the Left(), Right(), and
> Replace() functions to insert characters into a returned list of my
> date field. The date currently looks like this "06202006" the function
> I am looking for would return "06/20/2006" something like this if
> Insert() was an actual function:
> SELECT DISTINCT insert("/", right(insert("/",left(date, 2)), 2)
> FROM tblLogs;
> Please let me know if you have any experience with this.
>|||Works Great! I would like to format in the presentation layer of the
code, but I'm having troble running that formatting code through a
datgrid returning close to 10K entries. For some reason just works a
lot faster server side. Thanks for the help!

format datetime without characters

I need to convert the date format with an output of yyyymmdd'
I have tried the following code but it doesn't produce the desired output
CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
Any suggestions?
Thanks in advanceThat should work if supp_creation_date is a DATETIME or SMALLDATETIME. What
result do you get? Can you post some code to reproduce it? What version of
SQL Server? Here's an example:
SELECT CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,112
)
Result:
20050502
(1 row(s) affected)
If you get something else then I'd guess that in the context in which you
are using it the result is being implicitly cast to some other datatype.
David Portas
SQL Server MVP
--|||That seems to work for me:
Declare @.Date DateTime
Set @.Date = Current_TimeStamp
Select Convert(VarChar(8), @.Date, 112)
Produces:
20050502
What output are you getting?
Thomas
"Sherry" <Sherry@.discussions.microsoft.com> wrote in message
news:99360DE6-995D-45B2-80E0-744A800DE396@.microsoft.com...
>I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Sherry,
What is the type of column [supp_creation_date]?. If it is not a datetime
then you have to convert it to, before using the statement you posted.
Example:
declare @.s varchar(10)
set @.s = '05/02/2005'
select convert(char(8), convert(datetime, @.s, 101), 112)
go
-- this works
select convert(char(8), getdate(), 112)
go
AMB
"Sherry" wrote:

> I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Sherry, Your column in your table must not be a datetime... you'll have to
cast it to a datetime first.
Try this
Select CONVERT(VARCHAR(8), Cast(supp_creation_date As DateTime) , 112 )
From YourTable
"Sherry" wrote:

> I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Does it produce the proper format in Query Analyzer? This is always the
first place to try stuff out, as QA is very good not to insert it's own
formatting to the output:
create table test
(
supp_creation_date datetime
)
insert into test
select getdate()
go
select CONVERT(VARCHAR(8),supp_creation_date , 112 )
from test
returns:
20050502
However, if this value gets put back into a datetime variable before the
client recieves it, or the client puts it inot a date container, the
formatting goes away. Formatting only works on textual values, not date
values.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Sherry" <Sherry@.discussions.microsoft.com> wrote in message
news:99360DE6-995D-45B2-80E0-744A800DE396@.microsoft.com...
>I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance

Format a number to be a date format

I have a number field that looks like this:
8 characters long yyyymmdd
ie: 20051201
I can not change its type to a date field. In Access and Crystal I was able
to manipulate the number to make it look like a date ie: 12/01/2005
Can anyone help me figure out how to format the field like this?
thanks so much
--
Jeanne Conde, MCPWell, i guess it can be achieved by code snipet, pass value as argument
in code block and through VB syntax manipulate string in ne form.
Hope it helps|||The VB.NET function Data.Parse will be the way to go.|||Right click the field, click on properties.
In the 'format' section, choose cuistom and enter dd/mm/yyyy
That should do the trick
"Jeanne Conde" wrote:
> I have a number field that looks like this:
> 8 characters long yyyymmdd
> ie: 20051201
> I can not change its type to a date field. In Access and Crystal I was able
> to manipulate the number to make it look like a date ie: 12/01/2005
> Can anyone help me figure out how to format the field like this?
> thanks so much
> --
> Jeanne Conde, MCP