title: SQL Server: BULK INSERT from file that contains dates in format YYYYMMDD
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/10414995/bulk-insert-a-date-in-yyyymm-format-to-date-field-in-ms-sql-table">bulk insert a date in YYYYMM format to date field in MS SQL table</a>  </p>
  </blockquote>
  
  
  
  <p>I am using SQL Server 2012 Express.</p>
  
  <p>I have a problem with using BULK INSERT from file that contains dates in format YYYYMMDD (can't change the format, it's an output from different software). I use a format file, as I have an identity column that should be skipped when inserting values. Table structure and BULK INSERT command are here:</p>
  
  <pre><code>CREATE TABLE [dbo].[daily](
  [id] [bigint] IDENTITY(1,1) NOT NULL,
  [ticker] [varchar](15) NOT NULL,
  [ddate] [date] NOT NULL,
  [dopen] [decimal](16, 8) NOT NULL,
  [dhigh] [decimal](16, 8) NOT NULL,
  [dlow] [decimal](16, 8) NOT NULL,
  [dclose] [decimal](16, 8) NOT NULL,
  [dvol] [int] NOT NULL,
  [dopenint] [int] NOT NULL
  );
  
  
  BULK INSERT daily
  FROM 'C:\IBM.TXT'
  WITH (
  FORMATFILE = 'C:\dailyformat.xml',
  TABLOCK,
  FIRSTROW = 2);
  </code></pre>
  
  <p>Tab-delimited data file (IBM.TXT) is here:</p>
  
  <pre><code>Symbol  Date     Open    High    Low     Close   Total Volume    Total Open Interest
  IBM 19620102     2.57000000  2.57000000  2.54000000  2.54000000    11704           0
  IBM 19620103     2.54000000  2.56000000  2.54000000  2.56000000     8778           0
  IBM 19620104     2.56000000  2.56000000  2.54000000  2.54000000     7878           0
  IBM 19620105     2.53000000  2.53000000  2.48000000  2.49000000    11029           0
  IBM 19620108     2.49000000  2.49000000  2.42000000  2.44000000    16431           0
  IBM 19620109     2.45000000  2.50000000  2.45000000  2.47000000    14855           0
  </code></pre>
  
  <p>Format file (dailyformat.xml), generated by bcp, is here:</p>
  
  <pre><code>&lt;?xml version="1.0"?&gt;
  &lt;BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
   &lt;RECORD&gt;
    &lt;FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="15"/&gt;
    &lt;FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="11"/&gt;
    &lt;FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="41"/&gt;
    &lt;FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="41"/&gt;
    &lt;FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="41"/&gt;
    &lt;FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="41"/&gt;
    &lt;FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/&gt;
    &lt;FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="12"/&gt;
   &lt;/RECORD&gt;
   &lt;ROW&gt;
    &lt;COLUMN SOURCE="1" NAME="ticker" xsi:type="SQLVARYCHAR"/&gt;
    &lt;COLUMN SOURCE="2" NAME="ddate" xsi:type="SQLDATE"/&gt;
    &lt;COLUMN SOURCE="3" NAME="dopen" xsi:type="SQLDECIMAL" PRECISION="16" SCALE="8"/&gt;
    &lt;COLUMN SOURCE="4" NAME="dhigh" xsi:type="SQLDECIMAL" PRECISION="16" SCALE="8"/&gt;
    &lt;COLUMN SOURCE="5" NAME="dlow" xsi:type="SQLDECIMAL" PRECISION="16" SCALE="8"/&gt;
    &lt;COLUMN SOURCE="6" NAME="dclose" xsi:type="SQLDECIMAL" PRECISION="16" SCALE="8"/&gt;
    &lt;COLUMN SOURCE="7" NAME="dvol" xsi:type="SQLINT"/&gt;
    &lt;COLUMN SOURCE="8" NAME="dopenint" xsi:type="SQLINT"/&gt;
   &lt;/ROW&gt;
  &lt;/BCPFORMAT&gt;
  </code></pre>
  
  <p>I get the following error:</p>
  
  <pre><code>Msg 206, Level 16, State 2, Line 1
  Operand type clash: numeric is incompatible with date.
  </code></pre>
  
  <p>Please help to fix it without inserting date as number to a temporary table and then converting it to date by selecting it into other table. There should be some fix to my code.</p>
  
  <p>Thanks!</p>