Bulk insert using txt file in SQL table which contains date gives back Msg 7339 invalid data for column date

Question

Trying to execute a bulk insert statement in sql table which contains dates.

Bulk insert Games
From 'C:\a\sss\a\Games.txt'
With (FIRSTROW =2 , FIELDTERMINATOR ='\t', ROWTERMINATOR='\n');

I keep getting an error

Msg 7339
Invalid data for column date

The column date in the table is of type date.

In the .txt file the value is 31-Dec-18.

Can someone please help on this?

Database built using SQL Server Management Studio 11.0

And to provide some info on the file to be loaded:

date            
31-Dec-17           
31-Dec-17

Have you tried formatting the date like this https://stackoverflow.com/a/10415027/311255 — SelvaS, Jul 12 '18 at 15:56
also read this https://www.mssqltips.com/sqlservertip/5206/sql-server-datetime-best-practices/ — GuidoG, Jul 12 '18 at 16:00
Sounds like too much work i was trying to avoid since I will need to save in a temp table and then automatically identify all date columns and cast them on the go. — user2719567, Jul 12 '18 at 18:17
What about using since i am doing this over vb program the sqlbulk function. — user2719567, Jul 12 '18 at 18:17
Have you considered using the [SSMS file import wizard](https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-flat-file-wizard?view=sql-server-2017)? (Or the Import Data wizard would probably work too...) — Richardissimo, Jul 12 '18 at 21:14
How about reading the data into a varchar (or nvarchar) columns, and then converting to a date/datetime when you select the data out? (Or you could do an update and convert the text field to a date field in a different column.) — BoCoKeith, Jul 12 '18 at 22:29
SSMS file import wizard is not something i can use as this will happen automatically through the code. So the only option is to write the file in a temp table and then transfer the table by converting it ? — user2719567, Jul 14 '18 at 16:20

0 Answers0