I am attempting to import some data from the text file into an SQL database during my Visual Studio project. All of the posts have imported properly aside from the date column. I made use of the next command to import the information:
BULK INSERT T2 FROM 'c:\Temp\Data.txt' WITH (FIELDTERMINATOR = ',')
Within the text file i've dates like 02-02-12, 03-02-12, etc however in the database, all rows happen to be set to 01/01/1900. I figured this may have happened since the date formats will vary within the text file evaluating towards the SQL database, does anybody understand how i possibly could import my dates in to the database?
I encounter exactly the same problem making use of your example in 2008r2. No error but ready to 1900-01-01. Looks it is a quirk in bcp when accustomed to populate DATE area. It reacts by doing this just for DATE column type
If you use Date type, change it out to Datetime or smalldatetime (both labored for me personally) and find out whether it helps. Make sure to set dateformat so you've days, several weeks and years correctly read. If it's day-month-year (in text file) it ought to be:
set dateformat dmy BULK INSERT T2 FROM 'c:\Temp\Data.txt' WITH (FIELDTERMINATOR = ',')
I found one set of it, but no definitive answer why this occurs: similar case reported here