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.