Importing Dates via DTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Importing Dates via DTS

Author

Topic

ewade
Starting Member

36 Posts

Posted - 2003-08-01 : 14:17:51

I'm having an interesting problem trying to import data from an Excel spreadsheet into a SQL2000 table. I've created a named range in the source file that includes three datetime columns, one of which contains nulls.

The two non-null columns have times specified in 24-hour notation (14:35, etc.). The destination columns in the existing table are also of type datetime. During the import, DTS chokes on the first row and gives a (DBTYPE_DATETIMESTAMP) overflow error on the first mandatory datetime column.

I've tried to solve this problem a number of ways:

Exporting the file as a CSV
Converting the destination columns' datatypes to smalldatetime
Changing the definition and formatting of the source columns
Importing the data via the application's Access front-end

Each time, I receive the same error message. If I'm guessing correctly, DTS is trying to alter the datatype in transit (which it shouldn't need to do since they are of the same type).

Obviously I'm missing something here. Can anyone offer advice on how to make this import process work successfully?

Never stop learning!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-01 : 19:05:04

Strange thing but
DataType (Format) of those Excel's cells must be Numeric,
not Date nor Text types. Just change their format (even
if date values (e.g. 22.02.2003 14:53:00) already are there).

- Vit

ewade
Starting Member

36 Posts

Posted - 2003-08-04 : 11:59:37

Actually, those columns are date columns. That's why I even tried exporting a CSV file and importing it via DTS... in the hopes that Excel was just being difficult.

Never stop learning!

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-04 : 12:28:17

Hey, just for kicks...import it in to a tables where all the columns are varchar(x)

Then, SELECT from this table the column that is suppose to be a date with something like:

SELECT * FROM newTable
WHERE ISDATE(myDateCol) = 0

That will show you all rows that SQL soesn't think of as a date.

Lettuce know how it goes...

Brett

8-)

SELECT POST=NewId()

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-04 : 13:02:57

>Actually, those columns are date columns.

I understood nothing. Well, I open a new workbook. Then
I write in A1 cell '22.02.2002 14:53:00'. No matter is it
DateTime Type cell or General Type cell or Text Type cell -
Data Pump will not work.
Now I change data type of this cell into Numeric Data Type
and my datetime value in this cell changes too (namely, in
case of above sample into number 37309,6201388889)...

And only after this trick Data Pump will do its job >> in my
SQL table in its DateTime column I see the right value (Feb 22...).

Or you just have no right to change data type of your Excel's range?

- Vit

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-04 : 14:25:19

Or use a query instead of the Excel sheet as SourceTable for DataPump.

select CDbl(a) a, CDbl(b) b, CDbl(c) c from [Sheet1$]

- Vit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources