title: Using Default constraint during BULK INSERT where data conversion error occurs
body: <p>I'm attempting to use BULK INSERT to insert 3rd party reports. One of the columns has call duration using TIME data type, however if for some reason they don't have a call time they have the report label it as N/A which results in errors during the BULK INSERT:
  <code>Msg 4864, Level 16, State 1, Line 20
  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 11 (call_duration).</code></p>
  
  <p>The column in question <code>call_duration</code> has a default constraint of '00:00:00' I would like the system to use if/when there is an error flagged during the BULK INSERT in that column (or any column for that matter with a default constraint)</p>
  
  <p>UPDATE: Here's my BULK INSERT statement:</p>
  
  <pre><code>BULK INSERT dbo.TempYellowPages
  FROM 'Z:\YP.txt'
  WITH (
  FIRSTROW=2,
  FIELDTERMINATOR='\t',
  ROWTERMINATOR='\n',
  MAXERRORS = 99
  )
  </code></pre>
  
  <p>I'm looking to use the default constraint of the columns within the <code>TempYellowPages</code> table when there's an issue with the data. I can't use CONVERT (to my knowledge) as the data isn't in a source table, it's coming directly from a file. Here's an example of some of the fields the file could have:</p>
  
  <pre><code>Date    Time    Caller Name Caller Number   Call Duration
  9/2/2015    4:03:18 PM  John Smith  (555) 444-1115  0:04:38
  9/2/2015    10:53:09 AM Thomas Bush (555) 444-1115  N/A
  9/2/2015    10:26:28 AM Burt Fenimore   (555) 444-1115  0:05:53
  </code></pre>