title: SQL Server BULK INSERT - Inserting DateTime values
body: <p>I have 6 million rows worth of data I want to insert into my SQL Server database. I can do it the slow way with 6 million INSERT statements (by my calculation it would take 18 hours to run) or I can try BULK INSERT.</p>
  
  <p>BULK INSERT has issues with not being able to escape characters, but the data in this case is very simple and so shouldn't run into this problem.</p>
  
  <p>However SQL Server doesn't seem to like any form of date/time data to be inserted into a field.</p>
  
  <p>Here is the table (psuedo-SQL)</p>
  
  <pre><code>CREATE TABLE Tasks (
      TaskId bigint NOT NULL IDENTITY(1,1) PRIMARY KEY,
      TriggerId bigint NOT NULL FOREIGN KEY,
      Created datetime NOT NULL,
      Modified datetime NOT NULL,
      ScheduledFor datetime NULL,
      LastRan datetime NULL,
      -- and about 10 more fields after this
  )
  </code></pre>
  
  <p>Here is my BULK INSERT statement:</p>
  
  <pre><code>SET DATEFORMAT dmy
  BULK INSERT Tasks
  FROM 'C:\TasksBulk.dat'
  WITH (
      -- CHECK_CONSTRAINTS is not necessary as the only constraints are always enforced regardless of this option (UNIQUE, PRIMARY KEY, and NOT NULL)
      CODEPAGE = 'RAW',
      DATAFILETYPE = 'native',
  
      KEEPIDENTITY,
      MAXERRORS = 1,
      ORDER ( CallId ASC ),
  
      FIELDTERMINATOR = '\t',
      ROWTERMINATOR   = '\0'
  )
  </code></pre>
  
  <p>And here is the first row of data in TasksBulk.dat:</p>
  
  <pre><code>1000\t1092\t01/01/2010 04:00:17\t01/01/2010 04:00:17\t\t01/01/2010 04:00:14\0
  </code></pre>
  
  <p>(For readability, reformatted with tab characters replaced with 4-spaces:)</p>
  
  <pre><code>1000    1092    01/01/2010 04:00:17    01/01/2010 04:00:17        01/01/2010 04:00:14\0
  </code></pre>
  
  <p>However when I run the BULK INSERT statement, I get this error:</p>
  
  <blockquote>
    <p>Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error
    (type mismatch or invalid character for the specified codepage) for
    row 1, column 3 (Created).</p>
  </blockquote>
  
  <p>I have tried using different row and field terminators and every different date/time format (including "01/01/2010", "2010-01-01", both with and without the "04:00:17" time component). I don't know what I'm doing wrong here.</p>