For SQL Server 2014 - text using "bulk insert"

Question

SQL Server 2014: is it possible to import a .text file with a date in DD/MM/YYYY format, using BULK INSERT ?

I'm getting this error:

Msg 4864, Level 16, State 1, Line 56
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 14 (Invoice_date).

Invoice_date is of smalldatetime datatype.

Answer 1

Change the default session date format prior the executing the BULK INSERT with the following T-SQL statement:

SET DATEFORMAT DMY

Remember to revert back to the original session setting to avoid misinterpretation of ambiguous date formats. Ideally, change the field format to YYYYMMDD so that it will work correctly regardless of the DATEFORMAT setting.