How to import CSV with unrecognized datetime format?

This is how the table looks like:

CREATE TABLE [dbo].[temptable] 
    (
        [id] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [datetime] [datetime] NOT NULL, 
        [status] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
        [col4] [money] NULL,
        [col5] [float] NULL,
        [col6] [money] NULL,
        [col7] [float] NULL,
        [col8] [money] NULL,
        [total] [money] NOT NULL
    )

This is how the CSV looks like:

"ID","Date","status","Total"
"1611120001","12/11/2016 10:06 AM","closed","8.15"
"1611120002","12/11/2016 10:14 AM","closed","21.25"
"1611120003","12/11/2016 10:24 AM","closed","10.75"
"1611120004","12/11/2016 10:39 AM","closed","10.90"
"1611120005","12/11/2016 10:46 AM","closed","30.10"
"1611120006","12/11/2016 11:04 AM","closed","7.40"

This is how my format file looks like:

10.0
4
1   SQLCHAR 0   50  "," 1   sale_id ""
2   SQLDATETIME 0   8   "," 2   sale_datetime   ""
3   SQLCHAR 0   50  "," 3   sale_status ""
4   SQLMONEY    0   8   "\n"    9   grand_total ""

The SQL script:

SET DATEFORMAT dmy
BULK INSERT temptable
    FROM 'C:\backup\temp.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n',   
    FORMATFILE = 'C:\backup\temp_format.txt'
    )

I'm getting this error when I try to execute the script:

Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 2 (datetime).

I think datetime won't accept the format of date like this 12/11/2016 10:06 AM, but this CSV file is created by a third party software, so what can I do?

Solution

I would expect BULK INSERT to handle the file datetime format. The issue is with the format file specification. You need to:

specify a dummy field for the quote field enclosure of the first field
specify SQLCHAR for all file fields (describes the file, not the table)
specify the quote field enclosures in the field terminator

If the rows are terminated with both carriage return and newline characters, specify "\"\r\n" instead of "\"\n" as the last field terminator.

The format file for the specified data should be:

10.0
5
1   SQLCHAR 0 0 "\""     0 field1         ""
2   SQLCHAR 0 0 "\",\""  1 sale_id        ""
3   SQLCHAR 0 0 "\",\""  2 sale_datetime  ""
4   SQLCHAR 0 0 "\",\""  3 sale_status    ""
5   SQLCHAR 0 0 "\"\n"   9 total            ""

Because the format file specifies the field and row terminators, you can omit those keywords from the BULK INSERT statement:

BULK INSERT temptable
FROM 'C:\backup\temp.csv'
WITH
    (
        FIRSTROW = 2,
        FORMATFILE = 'C:\backup\temp_format.txt'
    );