Databases
StackOverflow2013
Postsdbo
POBulk load data conversion error while importing DATETIME data
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBulk load data conversion error while importing DATETIME data
    text
    copied!<p>I found a few posts on this topic on StackOverflow, but none seem to solve my problem.</p> <p>I am trying to set up bulk imports for SQL Server 2008 Express, and it is failing to import <code>datetime</code> values. The issue seems so basic that I must be missing something very simple, and I'm hoping someone else can catch the problem.</p> <h2>The Problem</h2> <p>I am importing into this table:</p> <pre><code>CREATE TABLE [dbo].[BulkTest]( [ReportDate] [datetime] NOT NULL ) </code></pre> <p>This is my format file (BulkTest.fmt):</p> <pre><code>10.0 1 1 SQLDATETIME 0 0 "\r\n" 1 ReportDate "" </code></pre> <p>This is the data being imported (BulkTest.tab):</p> <pre><code>ReportDate 2010-12-31 2011-01-31 </code></pre> <p>This is the import statement:</p> <pre><code>BULK INSERT dbo.BulkTest FROM 'Q:\...\BulkTest.tab' WITH ( CHECK_CONSTRAINTS, TABLOCK, FORMATFILE='Q:\...\BulkTest.fmt', FIRSTROW=1, DATAFILETYPE='char' ); </code></pre> <p>These are the errors:</p> <pre><code>Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (ReportDate). Msg 4864, Level 16, State 1, Line 1 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (ReportDate). Msg 4832, Level 16, State 1, Line 1 </code></pre> <h2>I Have Tried</h2> <ul> <li>Changing the date format, including <code>12/31/2010</code>, <code>31/12/2010</code>, <code>20101231</code>, <code>2010-12-31 00:00:00</code>, and various other formats.</li> <li>Adding/removing/changing bulk insert statement options <code>DATAFILETYPE</code>, <code>TABLOCK</code>, <code>CHECK_CONSTRAINTS</code>.</li> <li>Changing the delimiter and the field size in the format file (though per MSDN the field size should not matter).</li> <li>Running <code>SET DATEFORMAT ymd</code>.</li> <li>Checking the imported file with hex editor to make sure that it really does contain 8-bit chars and not unicode; it contains exactly what is shown in ASCII format.</li> </ul> <p>Any ideas?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload