CODEWITHSUNDEEP
sqldatabasevisual-studio-2010
Blob
Blob

Reputation: 541

Import data into SQL database from txt file

I'm trying to import some data from a text file into an SQL database in my Visual Studio project. All the columns have imported correctly apart from the date column. I used the following command to import the data:

BULK INSERT T2 FROM 'c:\Temp\Data.txt' WITH (FIELDTERMINATOR = ',')

In the text file i have dates like 02-02-12, 03-02-12, etc but in the database, all rows have been set to 01/01/1900. I thought this might have occured because the date formats are different in the text file comparing to the SQL database, does anyone know how i could import my dates into the database?

Thanks

Upvotes: 2

Views: 4926

Answers (1)

wmz
wmz

Reputation: 3685

I run into the same problem using your example in 2008r2. No error but all set to 1900-01-01. Looks it's a quirk in bcp when used to populate DATE field. It behaves this way only for DATE column type

So if you use Date type, change it to Datetime or smalldatetime (both worked for me) and see if it helps. Remember to set dateformat so you have days, months and years properly read. If it is day-month-year (in text file) it should be: 

set dateformat dmy
BULK INSERT T2 FROM 'c:\Temp\Data.txt' WITH (FIELDTERMINATOR = ',')

I was able to find one report on it, but no definitive answer why this happens: similar case reported here

Upvotes: 1

Related Questions