Web Development

Ask something
Discussion Locked

SQL Server 2000 and DTS import Date/Time

0 helpful
4 replies

by netadmingovernment

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi!
I am trying to import a flat text file into a SQL Server table and the text file has a time field that when I import it into SQL Server (and the sql server table is defined as smalldatetime or datetime) it imports but changes 12:00 PM to 00:00 PM - does anyone know why this is doing this? I have tried to edit transformation to HH:mm tt and to hh:mm tt and neither of them give me the right time. If I run the import and change the SQL Server table to varchar it imports correctly but I can't run time functions against it - so I can't do that.
Does anyone have any ideas, I am desparate.
Thanks
Gina

4 replies

by sgt_shultz

well, it sounds like it is not getting a valid entry for time field so it is putting in null time or something. i would make test import file and diddle with formatting until one worked. maybe it really wants serial number time, not hours:minutes format, like that...or try appending a fake day month and year to the time field in the test flat file, see if it will swallow it that way, see?

by netadmingovernment

Poster rated this answer.

I have tried every combination and nothing works.
This is the error I get:
he number of failing rows exceeds the maximum specified. Insert error Col 16,17 to STRT, FNSH, DB_TYPE_DBTIMESTAMP, status 6 Data Overflow.
Invalid character value for cast specification.

I have checked and triple checked and I have the right fields going to the right columns.
thanks for you ideas and quick response

by eppk

Try to work around with the MS.Access -- first
import into Access first then SQL

by netadmingovernment

I sloved my own problem - the dates are in a correct format in the text file and when the DTS package is executed it doesn't work right with the smalldatetime type - so in the DTS package I create and drop the table each time the data is imported and when I create the DB I leave the data types as varchar and then I convert them to smalldatetime and then functions can be run against them. This is working. Thanks for all the help:)

This discussion is closed to new replies.