title: Azure Data Factory Date Conversion
body: <p>I am just starting to learn Data Factory and I have a very simple pipeline that is loading a CSV file from Azure Blob Storage into Azure SQL Data Warehouse.</p>
  
  <p>The source CSV file column 3 includes dates in the format of <code>MM/dd/yyyy</code> and my target SQL DW table has this column typed as date.</p>
  
  <p>My pipeline fails with the following error, presumably because I am not in US and so an implicit type conversion won't work:</p>
  
  <blockquote>
    <p>Activity BlobToSQLDW failed: ErrorCode=FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error happened when loading data into SQL Data Warehouse.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopSqlException: Error converting data type VARCHAR to DATETIME.,Source=.Net SqlClient Data Provider,SqlErrorNumber=107090,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=107090,State=1,Message=HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopSqlException: Error converting data type VARCHAR to DATETIME.,},],'</p>
  </blockquote>
  
  <p>I have tried making edits to the schema for the blob source, including setting format and culture (see screenshot below) but to no avail yet.</p>
  
  <p><strong>Azure Blob Storage Schema screenshot:</strong></p>
  
  <p><img src="https://i.stack.imgur.com/9tsSI.png" alt="Azure Blob Storage Schema screenshot"></p>
  
  <p>I did manage to workaround the issue by creating a staging table in SQL DW with all columns typed as varchar and changing the ADF pipeline to target that instead, and then I complete the task inside SQL DW using a T-SQL Insert (staging-to-target) to make the type conversions.</p>
  
  <p>I'm sure there is a proper way to do this within ADF though. Can anyone please advise?</p>