Time field DTS import error for datetime Data Type | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Time field DTS import error for datetime Data Type

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]
GO 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?

]]>