DB2 date & Time datatype migration to SQL Server datetime

Hi,

We are migrating our database from DB2 8 to SQL Server 2005. We have date and time saperate columns in DB2. For example, Date_of_birth, Store_sun_open_time, Store_sun_close_time etc. For date we are using datetime. For time what datatype should we use in SQL Server?

Thanks

PrashantThe SQL Server datetime data type includes both date and time. For instance here is the result of the sql statement selecting the current date and time:

select getdate()

returns the value

2006-02-15 06:47:45.270

You can create your datetime value as a string and populate your datetime variables from that string. The single space between the day and hour components in the string is critical.

You can use several date formats (see Books Online). I prefer the 'yyyy-mm-dd hh:mm:ss.ms" format in a 24 hour format. The datetime datatype precision is 3 milliseconds (ms). Missing time components will default to zero, so for example if you only have the hour and minutes value (i.e 14:45) the time portion of the column would be 14:45:00.000.

Hope this helps|||I guess I was not very clear with my question, I will rephrase.

In DB2 table I have columns for "Time" datatype. For example, store_sunday_close_time. No date is attached to it. Now when recreating this column SQL Server, what datatype should I use as there is not "Time" datatype. If we use "datetime" datatype, what date should we enter?|||If you do not have a date to associate with the time, you are out of luck. The datetime data type requires both, otherwise your date part will be set to 1900-01-01.

So it looks like you will have to us a char or varchar to store your time component unless you can live with 1900-01-01 as the date (for example you are going to subtract the store_close_time from the store_open_time.

Db Migration with Identity constraints and their Fkey records

Hi,
we have to migrate data from three tables in 3 different servers into one
table on fourth servers.. and the scenariou is as below
ServerA/Database : One table named as Access with Pkey and identity field.
This identity field is Fkey in another table.
ServerB/Database : One table named as Access with Pkey and identity field.
This identity field is Fkey in another table.
ServerC/Database : One table named as Access with Pkey and identity field.
This identity field is Fkey in another table.
We have to merge data/table of all the above three servers into one
database. We have to merge all records from all three servers into one tabl
e
which will be in another server.
How should we take care of identity issues and fkey constraints as table has
different identity in all three servers and they are related to some other
tables as Fkey Constraints. table structure is same in all servers.
Thanks in advance ,
SunnySunny
> We have to merge data/table of all the above three servers into one
> database. We have to merge all records from all three servers into one
> table
> which will be in another server.
Perhsps by using DTS Package to move the data but you will have to decide
how to store the data because what if these table have the same identity
value. It is up to you to decide. Ome option is to create a group of these
values (i mean identities) from three table and generate a PK to each
group.
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:760AF5E1-D44C-4888-BFB5-B4D61E4F7EB0@.microsoft.com...
> Hi,
> we have to migrate data from three tables in 3 different servers into one
> table on fourth servers.. and the scenariou is as below
> ServerA/Database : One table named as Access with Pkey and identity field.
> This identity field is Fkey in another table.
> ServerB/Database : One table named as Access with Pkey and identity field.
> This identity field is Fkey in another table.
> ServerC/Database : One table named as Access with Pkey and identity field.
> This identity field is Fkey in another table.
> We have to merge data/table of all the above three servers into one
> database. We have to merge all records from all three servers into one
> table
> which will be in another server.
> How should we take care of identity issues and fkey constraints as table
> has
> different identity in all three servers and they are related to some other
> tables as Fkey Constraints. table structure is same in all servers.
> Thanks in advance ,
> Sunny
>|||Thanks, but could you please provide more details what exactly you mean here
.
"Uri Dimant" wrote:

> Sunny
>
> Perhsps by using DTS Package to move the data but you will have to decide
> how to store the data because what if these table have the same identity
> value. It is up to you to decide. Ome option is to create a group of thes
e
> values (i mean identities) from three table and generate a PK to each
> group.
>
> "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
> news:760AF5E1-D44C-4888-BFB5-B4D61E4F7EB0@.microsoft.com...
>
>

DB Migration Project Plan

Anyone has a DB Migration Project Plan template or an example? Pls email it to Nomthandazod@.gpg.gov.zaDO you want to be a little more specific about what you need to do?|||Thanks for responding. I need to present a professional detailed plan of how i'll be moving databases of about 20GB each to a new blade server. One will first have to be converted from a Focus db to SQL Server 2k, and then moved to the new machine. I have designed and done the testing for the conversion, just need to present it and the plan to management!|||Just sent you a quick and easy way to do this, it has an attached zip file and is named move logins. Hope this helps you. Good thing is it will maintain the sid's of the users and db, so little as far as security will need to be changed/modified.

ughh just read the reply you posted, not sure if the one I sent will help you at all.

DB migration

I 've to migrate several DB between two SQL2000 servers .
I'm planning to do it using the detach/attach method which I've been using
successfully several times.
This time , however , I need also to migrate DB maintanence plan, jobs , as
well as DTS .
I've read several KB but couldn't find info regarding migration of
maintanence plan.
As per DTS how's the best method to migrate the DTS definitions ?
ThanksYou can open up your packages and do a save as
stoney
"Stefano Colombo" wrote:

> I 've to migrate several DB between two SQL2000 servers .
> I'm planning to do it using the detach/attach method which I've been using
> successfully several times.
> This time , however , I need also to migrate DB maintanence plan, jobs , a
s
> well as DTS .
> I've read several KB but couldn't find info regarding migration of
> maintanence plan.
> As per DTS how's the best method to migrate the DTS definitions ?
> Thanks
>