Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    46

    Unanswered: 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

    Prashant

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    The 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:

    Code:
    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

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Mar 2004
    Posts
    46
    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?

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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.

    -- This is all just a Figment of my Imagination --

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •