Converting SQL Server 2000 database to 2005 format

Hello!
We have attached our 500GB SQL Server 2000 databases on 2005 server. The
process took literally took 10 seconds. I was wondering if databases should
undergo low level conversion when moved to 2005 environment in order to take
full advantage of new features. Will simply attaching the databases do the
job?
We are planning to switch databases to 9.0 compatibility mode. I am aware
certain features have been deprecated. Besides that I am concerned we won't
be able to take full advantage of new 2005 features unless we do low level
conversion. If this is the case, is there a way to initiate this process
after attaching the database ?
Any help is greatly appreciated,
Igor
There really is no low level conversion to take place. Most of the changes
are meta data and with the system and resource databases. It is highly
recommended that you run sp_updatestats after you attach it or better yet
after you set the mode to 9.0. Once you do that you will have full access
to the 2005 feature set.
Andrew J. Kelly SQL MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:OMvyLypLGHA.2216@.TK2MSFTNGP09.phx.gbl...
> Hello!
> We have attached our 500GB SQL Server 2000 databases on 2005 server.
> The process took literally took 10 seconds. I was wondering if databases
> should undergo low level conversion when moved to 2005 environment in
> order to take full advantage of new features. Will simply attaching the
> databases do the job?
> We are planning to switch databases to 9.0 compatibility mode. I am aware
> certain features have been deprecated. Besides that I am concerned we
> won't be able to take full advantage of new 2005 features unless we do low
> level conversion. If this is the case, is there a way to initiate this
> process after attaching the database ?
> Any help is greatly appreciated,
> Igor
>
|||Andrew,
Thanks a lot!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1f2TcqLGHA.1760@.TK2MSFTNGP10.phx.gbl...
> There really is no low level conversion to take place. Most of the
> changes are meta data and with the system and resource databases. It is
> highly recommended that you run sp_updatestats after you attach it or
> better yet after you set the mode to 9.0. Once you do that you will have
> full access to the 2005 feature set.
>
> --
> Andrew J. Kelly SQL MVP
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OMvyLypLGHA.2216@.TK2MSFTNGP09.phx.gbl...
>

Converting SQL Server 2000 database to 2005 format

Hello!
We have attached our 500GB SQL Server 2000 databases on 2005 server. The
process took literally took 10 seconds. I was wondering if databases should
undergo low level conversion when moved to 2005 environment in order to take
full advantage of new features. Will simply attaching the databases do the
job?
We are planning to switch databases to 9.0 compatibility mode. I am aware
certain features have been deprecated. Besides that I am concerned we won't
be able to take full advantage of new 2005 features unless we do low level
conversion. If this is the case, is there a way to initiate this process
after attaching the database ?
Any help is greatly appreciated,
IgorThere really is no low level conversion to take place. Most of the changes
are meta data and with the system and resource databases. It is highly
recommended that you run sp_updatestats after you attach it or better yet
after you set the mode to 9.0. Once you do that you will have full access
to the 2005 feature set.
Andrew J. Kelly SQL MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:OMvyLypLGHA.2216@.TK2MSFTNGP09.phx.gbl...
> Hello!
> We have attached our 500GB SQL Server 2000 databases on 2005 server.
> The process took literally took 10 seconds. I was wondering if databases
> should undergo low level conversion when moved to 2005 environment in
> order to take full advantage of new features. Will simply attaching the
> databases do the job?
> We are planning to switch databases to 9.0 compatibility mode. I am aware
> certain features have been deprecated. Besides that I am concerned we
> won't be able to take full advantage of new 2005 features unless we do low
> level conversion. If this is the case, is there a way to initiate this
> process after attaching the database ?
> Any help is greatly appreciated,
> Igor
>|||Andrew,
Thanks a lot!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1f2TcqLGHA.1760@.TK2MSFTNGP10.phx.gbl...
> There really is no low level conversion to take place. Most of the
> changes are meta data and with the system and resource databases. It is
> highly recommended that you run sp_updatestats after you attach it or
> better yet after you set the mode to 9.0. Once you do that you will have
> full access to the 2005 feature set.
>
> --
> Andrew J. Kelly SQL MVP
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OMvyLypLGHA.2216@.TK2MSFTNGP09.phx.gbl...
>> Hello!
>> We have attached our 500GB SQL Server 2000 databases on 2005 server.
>> The process took literally took 10 seconds. I was wondering if databases
>> should undergo low level conversion when moved to 2005 environment in
>> order to take full advantage of new features. Will simply attaching the
>> databases do the job?
>> We are planning to switch databases to 9.0 compatibility mode. I am aware
>> certain features have been deprecated. Besides that I am concerned we
>> won't be able to take full advantage of new 2005 features unless we do
>> low level conversion. If this is the case, is there a way to initiate
>> this process after attaching the database ?
>> Any help is greatly appreciated,
>> Igor
>

Converting SQL Server 2000 database to 2005 format

Hello!
We have attached our 500GB SQL Server 2000 databases on 2005 server. The
process took literally took 10 seconds. I was wondering if databases should
undergo low level conversion when moved to 2005 environment in order to take
full advantage of new features. Will simply attaching the databases do the
job?
We are planning to switch databases to 9.0 compatibility mode. I am aware
certain features have been deprecated. Besides that I am concerned we won't
be able to take full advantage of new 2005 features unless we do low level
conversion. If this is the case, is there a way to initiate this process
after attaching the database ?
Any help is greatly appreciated,
IgorThere really is no low level conversion to take place. Most of the changes
are meta data and with the system and resource databases. It is highly
recommended that you run sp_updatestats after you attach it or better yet
after you set the mode to 9.0. Once you do that you will have full access
to the 2005 feature set.
Andrew J. Kelly SQL MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:OMvyLypLGHA.2216@.TK2MSFTNGP09.phx.gbl...
> Hello!
> We have attached our 500GB SQL Server 2000 databases on 2005 server.
> The process took literally took 10 seconds. I was wondering if databases
> should undergo low level conversion when moved to 2005 environment in
> order to take full advantage of new features. Will simply attaching the
> databases do the job?
> We are planning to switch databases to 9.0 compatibility mode. I am aware
> certain features have been deprecated. Besides that I am concerned we
> won't be able to take full advantage of new 2005 features unless we do low
> level conversion. If this is the case, is there a way to initiate this
> process after attaching the database ?
> Any help is greatly appreciated,
> Igor
>|||Andrew,
Thanks a lot!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1f2TcqLGHA.1760@.TK2MSFTNGP10.phx.gbl...
> There really is no low level conversion to take place. Most of the
> changes are meta data and with the system and resource databases. It is
> highly recommended that you run sp_updatestats after you attach it or
> better yet after you set the mode to 9.0. Once you do that you will have
> full access to the 2005 feature set.
>
> --
> Andrew J. Kelly SQL MVP
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OMvyLypLGHA.2216@.TK2MSFTNGP09.phx.gbl...
>

Converting seconds to HHMMSS

My code calculates a duration of a start and end dates. It then converts the
duration into "
HHMMSS" format. My sample returns 0:0:3 which means 3 seconds.
Can someone help me modify my code so that the result would be 0:0:03 and
add the extra "padding 0" when the hours, minutes, or seconds are 1 digit?
CODE **************
declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
set @.dtStartDate = '20060314 01:39:14'
set @.dtEndDate = '20060314 01:39:17'
set @.duration = datediff(s,@.dtStartDate,@.dtEndDate)
select rtrim(@.duration/3600) + ':' + rtrim(@.duration % 3600/60) + ':' +
rtrim(@.duration
% 60)does this work for you?
declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
set @.dtStartDate = '20060314 01:39:14'
set @.dtEndDate = '20060314 01:39:17'
with 2 zeros
select convert(varchar,convert(datetime,
dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101' )),108)
one zero
select replace(convert(varchar,convert(datetime
,
dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101'
)),108),'00','0')
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||that'll do it. thanks.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145902342.424509.319660@.y43g2000cwc.googlegroups.com...
> does this work for you?
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
> set @.dtStartDate = '20060314 01:39:14'
> set @.dtEndDate = '20060314 01:39:17'
> with 2 zeros
> select convert(varchar,convert(datetime,
> dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101' )),108)
> one zero
> select replace(convert(varchar,convert(datetime
,
> dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101'
> )),108),'00','0')
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Scott,
Try using the function "right".
select right('00' + rtrim(@.duration/3600), 2) + ':' + right('00' +
rtrim(@.duration % 3600 / 60), 2) + ':' + right('00' + rtrim(@.duration % 60),
2)
Be careful with the solution posted by SQL, It does not seem to work for
duration greater than 24 hours.
AMB
"Scott" wrote:

> that'll do it. thanks.
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1145902342.424509.319660@.y43g2000cwc.googlegroups.com...
>
>|||> Be careful with the solution posted by SQL, It does not seem to work for
> duration greater than 24 hours.
Neither this one when the duration is greater than or equal to 100 hours. we
need to get more than two digits.

> select right('00' + rtrim(@.duration/3600), 2) <--
AMB
"Alejandro Mesa" wrote:
> Scott,
> Try using the function "right".
> select right('00' + rtrim(@.duration/3600), 2) + ':' + right('00' +
> rtrim(@.duration % 3600 / 60), 2) + ':' + right('00' + rtrim(@.duration % 60
),
> 2)
> Be careful with the solution posted by SQL, It does not seem to work for
> duration greater than 24 hours.
>
> AMB
> "Scott" wrote:
>

converting nvarchar to Minutes:Seconds in MS SQL?

I have a field in nvarchar type. It contains data like 0, :23, 1:57, ... all in minutes and seconds. Now, I need to convert it to MM:SS using query and get the Average of this column. How can I do it? I have tried Avg(Convert(nvarchar(20), [Calling Time], 108)) .. but I got error :The average aggregateoperation cannot take a nvarchar data type as an argument.

Help!!!! :(

You need to convert the minutes and seconds into some decimals which can be used in Avg function. Let's say you want to get Avg seconds, then you may try:

SELECT AvgSeconds= AVG(CASE WHEN CHARINDEX(':',[Calling Time])=0
THEN CONVERT(DECIMAL(4,2),[Calling Time])*60
WHEN CHARINDEX(':',[Calling Time])=1
THEN CONVERT(DECIMAL(4,2),RIGHT([Calling Time],LEN([Calling Time])-1))
ELSE CONVERT(DECIMAL(4,2),SUBSTRING([Calling Time],1,CHARINDEX(':',[Calling Time])-1))*60
+CONVERT(DECIMAL(4,2),SUBSTRING([Calling Time],CHARINDEX(':',[Calling Time])+1,LEN([Calling Time])))
END)
FROM test_CntTime