SQL Server Performance

Time field DTS import error for datetime Data Type

Discussion in 'SQL Server DTS-Related Questions' started by redbeans, Jan 19, 2006.

  1. redbeans New Member

    The DTS Import Data tool imports time field data from a text file into a table field with datetime Data Type using a base date (or zero integer date) of 12/30/1899 rather than the expected value 1/1/1900. Is this a known issue or an error on my part?

    My results:

    I created the following table structure with SQL 2000 sp4:
    CREATE TABLE [Test] (
    [Date] [datetime] NOT NULL ,
    [Time] [datetime] NOT NULL ,
    [Price] [smallmoney] NOT NULL ,
    [Size] [smallint] NOT NULL ,
    [DatePlusTime] AS ([Date] + [Time])
    ) ON [PRIMARY]

    I imported the following text file with the DTS Import tool:
    Date, Time, Price, Size
    12/21/2005, 07:52:27, 1.1926, 1
    12/21/2005, 07:52:41, 1.1928, 1
    12/21/2005, 07:52:49, 1.1927, 1

    Using EM Console Open Table I got the following display:
    Date, Time, Price, Size, DatePlusTime
    12/21/2005, 7:52:27 AM, 1.1926, 1, 12/19/2005 7:52:27 AM
    12/21/2005, 7:52:41 AM, 1.1928, 1, 12/19/2005 7:52:41 AM
    12/21/2005, 7:52:49 AM, 1.1927, 1, 12/19/2005 7:52:49 AM
    The Time field displayed here would indicate a zero-base date (zero integer) value of 1/1/1900 for the date portion of the data stored in the datetime data type field. However, the DatePlusTime field shows this to be incorrect since Date + Time do not properly combine to the same date. Rather they combine to a date two days earlier.

    Using Query Analyzer I got the following display:
    Date, Time, Price, Size, DatePlusTime
    2005-12-21 00:00:00.000, 1899-12-30 07:52:27.000, 1.1926, 1, 2005-12-19 07:52:27.000
    2005-12-21 00:00:00.000, 1899-12-30 07:52:41.000, 1.1928, 1, 2005-12-19 07:52:41.000
    2005-12-21 00:00:00.000, 1899-12-30 07:52:49.000, 1.1927, 1, 2005-12-19 07:52:49.000

    Sure enough, DTS has converted the text time fields to a datetime value with what appears to be a –2 integer value (date of 12/30/1899) rather than a 0 integer (date of 1/1/1900). This accounts for the DatePlusTime formula (Date+Time) yielding a date two days earlier than the date field.

    As a work-around I could always copy date and time into character fields and use VB to create a proper datetime value for each record. However, I am wondering if there is a more direct fix that I am missing. Would this be a bug with a patch that I missed or is this just a known anomaly?

Share This Page