convert all caps names to proper names

Is there a way in the report designer to format a field that contains names to display the name as a proper name rather than in all caps as stored in the db?

Thanks.

By proper name do you mean a name with just the first letter capitalized? You could write an expression for it. It would look something like this:

=iif(Fields!yourfield.Value.ToString.ToUpper = Fields!yourfield.Value.ToString, Fields!yourfield.Value.ToString.Chars(0).ToString & Fields!yourfield.Value.ToString.SubString(1, Fields!yourfield.Value.ToString.Length, Fields!yourfield.Value)

The expression assumes that the field will never be null and it will always be at least 2 characters long. If these aren't true it gets a little more complicated. Hopefully this puts you on the right track.

|||Due to the checks necessary, encapsulate this code into a function in the custom code section of the Report.

In Report Designer, add the following Function to the Code section of the Report properties dialog.

Public Function ToFirstUpper(str As String) As String
If (str = Nothing Or str.Length = 0)
Return String.Empty

If str.Length = 1 Then
Return str.ToUpper()
Else
Return str.Substring(0, 1).ToUpper() & str.Substring(1).ToLower()
End Function

This method is then called using the following expression.

=Code.ToFirstUpper(Fields!yourField.Value)

You can also do this directly in the SQL query. This example uses the SQL Server 2005 AdventureWorks database.

Select
UPPER(SUBSTRING(LastName, 1, 1)) + LOWER(SUBSTRING(LastName,2, LEN(LastName) - 1)) AS LastName
FROM Person.Contact

For more information on custom code:
Using Custom Code References in Expressions (Reporting Services)
How to: Add Code to a Report (Report Designer)|||If you want to use this function also outside reporting services take a look at:
http://vyaskn.tripod.com/code.htm#propercase

I haven't tried this sp yet, because I use Oracle as Datasource which has the function INITCAP..

so just rewrite your sql-statement:
select initcap(name), customerid from customers

or if you user MSSQL-Server as Source create the function in the link

convert adDBtimeStamp to datetime

Hello,
I use a SQL Server with ODBC driver and >NET C#.
I want to convert a date field with adDBtimeStamp format to a DATETIME
format, but no solution could be found till now.
My query is as follows:
SELECT received_at as EXPR1
FROM TTable
WHERE datepart(w(datetime(received_at)))>10
Has anybody any solution for conversion?
*** Sent via Developersdex http://www.examnotes.net ***What is the SQL datatype of "received_at" and what are you attepting to
do with it? adDBtimeStamp maps to a DATETIME type in SQL Server so no
conversion should be necessary. However your example code isn't valid
in SQL Server - there is no WEEK or DATETIME function and your syntax
for DATEPART is wrong.
Maybe the following was what you intended, assuming you are in fact
dealing with a DATETIME column:
SELECT received_at AS expr1
FROM TTable
WHERE DATEPART(WEEK,received_at)>10 ;
David Portas
SQL Server MVP
--|||
It seems that no conversion is made and no explicit conversion could be
applied.
for the sequence:
SELECT received_at AS expr1
FROM TTable
WHERE DATEPART(WEEK,received_at)>10
the outcome is:
Driver]Expected lexical element not found: )
*** Sent via Developersdex http://www.examnotes.net ***|||Lucian,
If the adDBtimeStamp values are seen as strings of the
form 'yyyymmddhhmmss', try this:
declare @.t table (
adDBtimeStamp char(14)
)
insert into @.t values ('20051012171534')
select
convert(datetime,
substring(adDBtimeStamp,1,8) + space(1) +
substring(adDBtimeStamp,9,2) + ':' +
substring(adDBtimeStamp,11,2) + ':' +
substring(adDBtimeStamp,12,2),
112) as SQLdt
from @.t
Steve Kass
Drew University
Lucian Baltes wrote:

>Hello,
>I use a SQL Server with ODBC driver and >NET C#.
>I want to convert a date field with adDBtimeStamp format to a DATETIME
>format, but no solution could be found till now.
>My query is as follows:
>SELECT received_at as EXPR1
>FROM TTable
>WHERE datepart(w(datetime(received_at)))>10
>Has anybody any solution for conversion?
>
>
>*** Sent via Developersdex http://www.examnotes.net ***
>

Convert Access 2003 Function

I am converting an Access DB to SQL2000 and opne of the queries uses the
Format$ function. How should the syntax be for SQL?
Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonthUse the CONVERT function with an optional date style argument. See the
following for syntax, samples and date arguments.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
HTH
Jerry
"dj5md" <dj5md@.discussions.microsoft.com> wrote in message
news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
>I am converting an Access DB to SQL2000 and opne of the queries uses the
> Format$ function. How should the syntax be for SQL?
> Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth|||Thanks Jerry, I already looked at Cast and Convert but cannot figure out the
correct syntax. It's a bit confusing for me.
"Jerry Spivey" wrote:
> Use the CONVERT function with an optional date style argument. See the
> following for syntax, samples and date arguments.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
> HTH
> Jerry
> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
> >I am converting an Access DB to SQL2000 and opne of the queries uses the
> > Format$ function. How should the syntax be for SQL?
> >
> > Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth
>
>|||Try this:
SELECT LEFT(CONVERT(VARCHAR(25),GETDATE(),112),6)
HTH
Jerry
"dj5md" <dj5md@.discussions.microsoft.com> wrote in message
news:8212D4FC-A530-47DC-9E07-934516904C6D@.microsoft.com...
> Thanks Jerry, I already looked at Cast and Convert but cannot figure out
> the
> correct syntax. It's a bit confusing for me.
> "Jerry Spivey" wrote:
>> Use the CONVERT function with an optional date style argument. See the
>> following for syntax, samples and date arguments.
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
>> HTH
>> Jerry
>> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
>> news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
>> >I am converting an Access DB to SQL2000 and opne of the queries uses the
>> > Format$ function. How should the syntax be for SQL?
>> >
>> > Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth
>>|||Thats was it. I changed the GetDate with my DB field and removed the Select
and it worked with no problems. Thanks much Jerry. I also understand the
function better now that I have the correct syntax.
"Jerry Spivey" wrote:
> Try this:
> SELECT LEFT(CONVERT(VARCHAR(25),GETDATE(),112),6)
> HTH
> Jerry
> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> news:8212D4FC-A530-47DC-9E07-934516904C6D@.microsoft.com...
> > Thanks Jerry, I already looked at Cast and Convert but cannot figure out
> > the
> > correct syntax. It's a bit confusing for me.
> >
> > "Jerry Spivey" wrote:
> >
> >> Use the CONVERT function with an optional date style argument. See the
> >> following for syntax, samples and date arguments.
> >>
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
> >>
> >> HTH
> >>
> >> Jerry
> >> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> >> news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
> >> >I am converting an Access DB to SQL2000 and opne of the queries uses the
> >> > Format$ function. How should the syntax be for SQL?
> >> >
> >> > Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth
> >>
> >>
> >>
>
>

CONVERT a text column to date format

I have a column nvarchar(8) that I need to update to a date format, MM/DD/YYYY

Some of the values have 7 characters. the rest have 8 characters as shown below:

Col1

6051998

12061999

In both rows the format is M/DD/YYYY and MM/DD/YYYY respectively. I have tried using CONVERT and CAST but receive the error:

Conversion failed when converting datetime from character string.

I've manage to generate the correct format in a select statement using CASE:

SELECT date_updted =

CASE

WHEN(selectLEN(date_updted))= 7 THEN(SELECTLEFT((RIGHT(date_updted, 7)), 1)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4)))

ELSE(SELECTLEFT((RIGHT(date_updted, 8)), 2)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4)))

END

FROM Table1

How can perform an update of this column using the UPDATE statement? I've tried the following with no success:

UPDATE dbo.Table1

SET date_updted =(SELECT date_updted =

CASE

WHEN(selectLEN(date_updted))= 7 THEN(SELECTLEFT((RIGHT(date_updted, 7)), 1)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4))As date_updted)

ELSE(SELECTLEFT((RIGHT(date_updted, 8)), 2)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4))As date_updted)

END

FROM Table1)

FROM Table1

If your data is as strongly formated as you say, you should be able to convert to datetime with something like:

Code Snippet

select aDate,
convert(datetime, right(aDate, 4) + left( right('0'+aDate, 8), 4))
as convertedDT
from ( select '6051998' as aDate union all
SELECT '12061999'
) a

/*
aDate convertedDT
--
6051998 1998-06-05 00:00:00.000
12061999 1999-12-06 00:00:00.000
*/

|||

Try something like this:

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
DateCol varchar(8)
)

INSERT INTO @.MyTable VALUES ( '6051998' )
INSERT INTO @.MyTable VALUES ( '12061999' )

SELECT convert( datetime, ( stuff( stuff( right( '0' + DateCol, 8 ), 3, 0, '/' ), 6, 0, '/' )), 101 )
FROM @.MyTable

-
1998-06-05 00:00:00.000
1999-12-06 00:00:00.000

|||

Thanks for your responses and I'm sure these methods will work fine, however, my question really was how to use the UPDATE Statement to update the column in my initial post without creating a temp table or column. Maybe I missed something in your responses?

|||

Copy the expression from those select statement...

Code Snippet

UPDATE

dbo.Table1

SET

date_updted =convert(datetime, right(date_updted, 4) + left( right('0'+date_updted, 8), 4))

FROM

Table1

|||

Maybe:

Code Snippet

UPDATE dbo.Table1
SET date_updted
= convert(varchar(10),convert(datetime, right(date_updted, 4) + left( right('0'+date_updted, 8), 4)),101)

|||Very Nice! Thanks.

convert a gregorian date in mm/dd/yyyy

hello,
I want to convert a gregorian date in mm/dd/yyyy format in MS SQL 2000.
:)Are you lookig to convert from one of the Gregorian calendars to the Julian calendar, or just present an existing DATETIME value in the format you specified? The conversion is an interesting problem, the presentation issue ought to be solved on the client rather than the server, although you can make yourself a LOT of trouble later on by just using:SELECT Convert(VARCHAR(30), GetDate(), 101)-PatPsqlsql

Convert a Date to a Day of the Week.

Hey, Im pretty sure this is possible, but let me know if it isnt.

I have a table of dates in the format "DD/MM/YYYY", is there a way of converting it to output the day of the week? for example 16/11/2007 would display Friday insted.

Thanks in advance John

selectdatepart(dw,GETDATE())

selectdatename(dw,GETDATE())

|||

Hi u can check out this

select datepart(dw,date_created) from users

select datename(dw,date_created) from users

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Thanks to the both of you, both methods worked amazingly faultlessly. Thanks John

Convert 1084313300 (Ten Digit) value to Datetime

I have a field in a table that contains ten digit value representing a datetime. Is there any way to convert it to default datetime format.
Plese Help me
Thanks
Message posted via http://www.sqlmonster.com
Imran Irfan via SQLMonster.com wrote:
> I have a field in a table that contains ten digit value representing
> a datetime. Is there any way to convert it to default datetime
> format. Plese Help me
> Thanks
We would need to know what format the 10 digit value was using in order
to convert it. Do you know what it is using? How do you currently
interpret the value in your application? i.e. is there an algorithm in
the application that clearly shows what's being done at the application
level?
David Gugick
Imceda Software
www.imceda.com
|||I assume that is a UNIX timestamp (seconds since 01/01/1970):
http://www.aspfaq.com/show.asp?id=2451
Jacco Schalkwijk
SQL Server MVP
"Imran Irfan via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b4d9fd153fd8404c87ca2328c54bcbd9@.SQLMonster.c om...
>I have a field in a table that contains ten digit value representing a
>datetime. Is there any way to convert it to default datetime format.
> Plese Help me
> Thanks
> --
> Message posted via http://www.sqlmonster.com

convert / group by date

Hi,
I have a datetime column named dtDateTime.
its format is "Oct 27 2006 12:00:00 "
I want to group by only date part of it and count

my code is

$sql1="SELECT convert(varchar,J1708Data.dtDateTime,120),
count(convert(varchar,J1708Data.dtDateTime,120))

FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleId

WHERE (J1708Data.iPidId = 303) AND
(J1708Date.dtDateTime between '2006-10-25' AND '2006-10-28')
AND (Vehicle.sDescription = $VehicleID)

GROUP BY convert(varchar,J1708Data.dtDateTime,120)";

However, convert part, group by part doesnt' work at all.
(i couldn't check count part)

can you find where's the problem?
Thx.kirke wrote:

Quote:

Originally Posted by

Hi,
I have a datetime column named dtDateTime.
its format is "Oct 27 2006 12:00:00 "
I want to group by only date part of it and count
>
my code is
>
>
$sql1="SELECT convert(varchar,J1708Data.dtDateTime,120),
count(convert(varchar,J1708Data.dtDateTime,120))
>
FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleId
>
WHERE (J1708Data.iPidId = 303) AND
(J1708Date.dtDateTime between '2006-10-25' AND '2006-10-28')
AND (Vehicle.sDescription = $VehicleID)
>
GROUP BY convert(varchar,J1708Data.dtDateTime,120)";
>
>
However, convert part, group by part doesnt' work at all.
(i couldn't check count part)
>
can you find where's the problem?

--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

I'd have done it like this (use VARCHAR(10) or CHAR(10) for the date
instead of an unspecified size):

SELECT CONVERT(VARCHAR(10),J.dtDateTime,120) As theDate,
COUNT(*) As theCount
FROM Vehicle As V INNER JOIN J1708Data As J
ON V.iID = J.iVehicleId
WHERE J.iPidId = 303
AND J.dtDateTime BETWEEN '2006-10-25' AND '2006-10-28 23:23:59'
AND V.sDescription = @.VehicleID
GROUP BY CONVERT(VARCHAR(10),J.dtDateTime,120)
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUrBwIechKqOuFEgEQKUTQCg1zGcAeAViDrJQWxENdcn2t xbhxYAoO4o
1Mks6W+FiXviMMrZi/lt4e3z
=vWR9
--END PGP SIGNATURE--|||On 2 Nov 2006 10:53:08 -0800, kirke wrote:

Quote:

Originally Posted by

>Hi,
>I have a datetime column named dtDateTime.
>its format is "Oct 27 2006 12:00:00 "
>I want to group by only date part of it and count
>
>my code is
>
>
>$sql1="SELECT convert(varchar,J1708Data.dtDateTime,120),
>count(convert(varchar,J1708Data.dtDateTime,120))
>
>FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleId
>
>WHERE (J1708Data.iPidId = 303) AND
>(J1708Date.dtDateTime between '2006-10-25' AND '2006-10-28')
>AND (Vehicle.sDescription = $VehicleID)
>
>GROUP BY convert(varchar,J1708Data.dtDateTime,120)";
>
>
>However, convert part, group by part doesnt' work at all.
>(i couldn't check count part)
>
>can you find where's the problem?
>Thx.

Hi kirke,

Have you tried to run the query? If so, what were the results? Were they
incoorrect, or did you get an error message. If the latter, then what
was that message?

I don't see any real problems with your data, thoough I would change a
few things:

* The date format. yyyy-mm-dd is not safe, becuase it can be interpreted
as yyyy-dd-mm for some country settings. Remve the dashes to get the
unambiguous yyyymmdd format.

* The use of BETWEEN means that rows with a startdate of 28th oct 2006
at exactly midnight will be included, but startdates on the same day
with a later time are excluded. The solution MGFoster proposes for this
(to include a time portion of 23:59:59) is not good enough - for
smalldatetime, this will be rounded up to the next minute, which is
midnight of the 29th of october; for datetime, you'll still miss rows
with a startdate in the last second of the day. You should replace
BETWEEN with a >= and a < condition:
AND J1708Date.dtDateTime >= '20061025'
AND J1708Date.dtDateTime < '20061029' -- Note the increased end day!
If you store all dates with the default time component of midnight, then
this is not necessary - but since it doesn't hurt either, I'd advice you
to accustom yourself to always using this techniques when comparing
datetimes.

The expression GROUP BY convert(varchar,J1708Data.dtDateTime,120) won't
group by daym, since the conversion doesn't chop off the time portion.
The result of select convert(varchar, current_timestamp, 120) for
instance is "2006-11-03 23:18:22", so you end up grouping by second.

Here's what I would try:

SELECT convert(varchar,J1708Data.dtDateTime,120),
count(convert(varchar,J1708Data.dtDateTime,120))
SELECT DATEADD(day, DATEDIFF(day, 0, d.DateTime), 0) AS TheDate,
COUNT(*) AS TheCount
FROM Vehicle AS v
INNER JOIN J1708Data AS d
ON v.VehicleID = d.VehicleId
WHERE d.PidId = 303
AND d.DateTime >= '20061025'
AND d.DateTime < '20061029'
AND v.Description = $VehicleID
GROUP BY DATEDIFF(day, 0, d.DateTime);

--
Hugo Kornelis, SQL Server MVPsqlsql

convert

Hi,
I need to change the format of a date column in one of the tables to reflect
a date that looks like mm/dd/yyyy. I used the following syntax that I know
it's wrong -it works in a select statement-:
alter table tbl_Reservation
convert(char(20),[from date],101) as [From Date]
Is there anyway to change the format on the table itself?
TSWhat datatype do you wish to have for that column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TS" <TS@.discussions.microsoft.com> wrote in message
news:CB5714C6-AC38-4C2B-B5F8-396CE43DAAA1@.microsoft.com...
> Hi,
> I need to change the format of a date column in one of the tables to refle
ct
> a date that looks like mm/dd/yyyy. I used the following syntax that I know
> it's wrong -it works in a select statement-:
> alter table tbl_Reservation
> convert(char(20),[from date],101) as [From Date]
> Is there anyway to change the format on the table itself?
>
> --
> TS|||I just need to change the format of that column from for example 2005-05-16
00:00:00 to a format of mm/dd/yyyy
TS
"Tibor Karaszi" wrote:

> What datatype do you wish to have for that column?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:CB5714C6-AC38-4C2B-B5F8-396CE43DAAA1@.microsoft.com...
>|||Tibor asked the data type of the field because if it's a datetime then
there's no way to "change the format" -- datetimes aren't stored in a
particular format, that's a display issue. If it's stored as a character typ
e
(which would probably be less than optimal), we would probably be able to
recommend some options.
"TS" wrote:
> I just need to change the format of that column from for example 2005-05-1
6
> 00:00:00 to a format of mm/dd/yyyy
> --
> TS
>
> "Tibor Karaszi" wrote:
>|||> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format"
Exactly. :-)
For more information, TS, I suggest you check out
http://www.karaszi.com/SQLServer/info_datetime.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"KH" <KH@.discussions.microsoft.com> wrote in message
news:E84225DE-A465-40C2-A343-246FF6551D42@.microsoft.com...
> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format" -- datetimes aren't stored in a
> particular format, that's a display issue. If it's stored as a character t
ype
> (which would probably be less than optimal), we would probably be able to
> recommend some options.
>
> "TS" wrote:
>|||Yes, the data type of the field is datetime. In the front-end, it appears in
the format mm/dd/yyyy which is the way I want it even though in the query I
ran from SQL it appears in the datetime format that there is no way to
change. I didn't know this is the case!!
Thanks a lot for your help.
--
TS
"KH" wrote:
> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format" -- datetimes aren't stored in a
> particular format, that's a display issue. If it's stored as a character t
ype
> (which would probably be less than optimal), we would probably be able to
> recommend some options.
>
> "TS" wrote:
>|||Hi,
You cant change the storage format if you use datetime data type. Only way
is during extraction you could use CONVERT function to format the date.
Alternatevely you could use the VARCHAR datatype to store the date format as
you need. Wile inserting you could format the field with CONVERT function.
But I recommend you to use datetime data type and while extraction you can
format it using CONVERT function.
Thanks
Hari
SQL Server MVP
"TS" <TS@.discussions.microsoft.com> wrote in message
news:FDF45283-3516-46A5-92FA-8AE1EF27AAE1@.microsoft.com...
> Yes, the data type of the field is datetime. In the front-end, it appears
> in
> the format mm/dd/yyyy which is the way I want it even though in the query
> I
> ran from SQL it appears in the datetime format that there is no way to
> change. I didn't know this is the case!!
> Thanks a lot for your help.
> --
> TS
>
> "KH" wrote:
>

Conversion of ASP reports into RDL files

Hi,
I need to know if we have any automated process of converting reports
generated in asp and dispalyed as html format can be converted to SQL
Reporting Service rdl files.
This will reduce my effort of re designing all the asp reports into the SQL
reports.
Please need an urgent help on this.
Thanks
Raghavunfortunately no. You will have to redesign except for MS access report where
you can convert. This is because you must have used your own logic inside
your ASP files, it will be difficult to convert automatically.
Amarnath
"raghav78" wrote:
> Hi,
> I need to know if we have any automated process of converting reports
> generated in asp and dispalyed as html format can be converted to SQL
> Reporting Service rdl files.
> This will reduce my effort of re designing all the asp reports into the SQL
> reports.
> Please need an urgent help on this.
> Thanks
> Raghav

conversion

Hi All,
I am pretty new to sql server. I have a column with data in the format
of #ddd,dddd
where d is a number between 0 and 9.
e.g. one entry of column #34,56
What is the easiest way to convert it to integer? #56,5-->565
Thanks a lot
Thanks.So the comma is not significant? If so, try:
select
cast (replace (replace (MyCol, '#', ''), ',', '') as int)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
<jack.smith.sam@.gmail.com> wrote in message
news:1162070389.388509.50190@.m7g2000cwm.googlegroups.com...
Hi All,
I am pretty new to sql server. I have a column with data in the format
of #ddd,dddd
where d is a number between 0 and 9.
e.g. one entry of column #34,56
What is the easiest way to convert it to integer? #56,5-->565
Thanks a lot
Thanks.|||If you want to permanently convert the column datatype to integer, use
Tom's suggestion to clear the non-numeric characters out of the current
column, and then use
ALTER TABLE MyTable
ALTER COLUMN Mycolumn int
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<jack.smith.sam@.gmail.com> wrote in message
news:1162070389.388509.50190@.m7g2000cwm.googlegroups.com...
> Hi All,
> I am pretty new to sql server. I have a column with data in the format
> of #ddd,dddd
> where d is a number between 0 and 9.
> e.g. one entry of column #34,56
> What is the easiest way to convert it to integer? #56,5-->565
> Thanks a lot
> Thanks.
>

Controlling Vertical Spacing in Table/Report

Developers,
I'm VERY new to MRS and I'm trying to format a report. I have a Header,
Detail, and Footer. In the Detail section, I have a table containing my
report items. Each cell contains variable(s).
Well, I have an address, and I want the Verticle Spacing between the lines
to be less than it currently is. I've been collapsing the table so that the
cells are so small I can't even read my variable names. This seems to work,
but I'm wondering:
Q. Is there a more graceful way to reduce the vertical spacing in my
table/report?
Please write soon! Thanks!!I dont think there is any. The only other thing you can try if making
the top and bottom padding 0.

Consuming all Memory

This is a multi-part message in MIME format.
--=_NextPart_000_003B_01C6948D.B1B75700
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
We have an application running in MSSQL 2000 with SP 4 and C#.NET 2003.
This server has 8 GB of memory and enough hard drive.
The MSSQL server keep consuming more and more memory and never release = it. It gets to the point that we have to reboot or the server crash.
The MSSQL memory start with 278432 kb of ram until it take all the = memory available in the server.
My question is..
What happend If I specify a minimum and maximun amount of memory. Will = MSSQL stop running when it get to the maximun or what will happned?
I will appreciate your help,
Rafael
--=_NextPart_000_003B_01C6948D.B1B75700
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
We have an application running in MSSQL = 2000 with SP 4 and C#.NET 2003.

This server has 8 GB of memory and = enough hard drive.

The MSSQL server keep consuming more = and more memory and never release it. It gets to the point that we have to = reboot or the server crash.

The MSSQL memory start with 278432 kb = of ram until it take all the memory available in the server.

My question is..

What happend If I specify a minimum and = maximun amount of memory. Will MSSQL stop running when it get to the maximun or = what will happned?


I will appreciate your = help,

Rafael
--=_NextPart_000_003B_01C6948D.B1B75700--This is a multi-part message in MIME format.
--=_NextPart_000_005B_01C694C0.9BB01E00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi
How to adjust memory usage by using configuration options in SQL Server:
http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;Q321363=20
-- Mike
This posting is provided "AS IS" with no warranties, and confers no =rights.
"Rafael Tejera" <rafaeltejera@.hotmail.com> wrote in message =news:OnAVm8KlGHA.836@.TK2MSFTNGP02.phx.gbl...
We have an application running in MSSQL 2000 with SP 4 and C#.NET =2003.
This server has 8 GB of memory and enough hard drive.
The MSSQL server keep consuming more and more memory and never release =it. It gets to the point that we have to reboot or the server crash.
The MSSQL memory start with 278432 kb of ram until it take all the =memory available in the server.
My question is..
What happend If I specify a minimum and maximun amount of memory. Will =MSSQL stop running when it get to the maximun or what will happned?
I will appreciate your help,
Rafael
--=_NextPart_000_005B_01C694C0.9BB01E00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi
How to adjust memory usage by using configuration options in SQL Server:
http://support.microsoft.com/default.aspx?scid=3Dkb;en-us=;Q321363 -- Mike
This posting is provided "AS IS" with no warranties, and confers no =rights.
"Rafael Tejera" = wrote in message news:OnAVm8KlGHA.836@.T=K2MSFTNGP02.phx.gbl...
We have an application running in =MSSQL 2000 with SP 4 and C#.NET 2003.

This server has 8 GB of memory and =enough hard drive.

The MSSQL server keep consuming more =and more memory and never release it. It gets to the point that we have =to reboot or the server crash.

The MSSQL memory start with 278432 kb =of ram until it take all the memory available in the server.

My question is..

What happend If I specify a minimum =and maximun amount of memory. Will MSSQL stop running when it get to the maximun =or what will happned?


I will appreciate your =help,

Rafael

---

--=_NextPart_000_005B_01C694C0.9BB01E00--

Construct a query.

Hi,

I have a table tab_temp of the format

FUNCTION_ID VARCHAR2(20),
DAILY_TARGET NUMBER,
DAILY_RESULT NUMBER,
DAILY_VARIANCE NUMBER,
WEEK1_TARGET NUMBER,
WEEK1_RESULT NUMBER,
WEEK1_VARIANCE NUMBER,
WEEK2_TARGET NUMBER,
WEEK2_RESULT NUMBER,
WEEK2_VARIANCE NUMBER,
WEEK3_TARGET NUMBER,
WEEK3_RESULT NUMBER,
WEEK3_VARIANCE NUMBER

No I want to fetch records in such a way that I display target first, and then result and then vairance.
e.g
1st record
function_id,daily_target,week1_target,week_2_targe t,week3_target
2nd record
function_id,daily_result,week1_result,week_2_resul t,week3_result
3rd record
function_id, daily_variance, week1_variance, week2_variance, week3_variance.

So one function_id should have 3 sets of records.
Now there could be several such function_ids.

How should I construct a query, (This is a requirement )which would fetch the records in above defined format.

Many thanks in advance.
Ashselect function_id
, 1 as line_number
, daily_target
, week1_target
, week2_target
, week3_target
from tab_temp
union all
select function_id
, 2
, daily_result
, week1_result
, week2_result
, week3_result
from tab_temp
union all
select function_id
, 3
, daily_variance
, week1_variance
, week2_variance
, week3_variance
from tab_temp
order
by function_id
, line_number|||I think this might work.

Many Thanks.