ADF Converting String to date from datetime in Copy Activity

Question

Hi Again! @Nasreen Akter

I am using the Query you taught me for another pipeline. I am saying:

SELECT *, FORMAT(CONVERT(datetime, Prop_5), 'yyyyMMdd') AS FormattedStartDate FROM [MercerStagingDev].[MILKY-WAYTEST\AppSQLVST4DotNetDev-R].[BCO_EMPS]

and remapping properly. But this time I am getting the following error. It worked on the last pipeline...any suggestions? I am just trying to convert the datetime in the DB (which should be varchar( I used auto-create) to just date in my output sink txt file (pipe delimited).....

Thanks!
Mike

Failure happened on 'Source' side.

ErrorCode=UserErrorFailedFileOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Upload file failed at path bco-cur-emps\BCO_EMP.txt.,Source=Microsoft.DataTransfer.Common,''Type=System.Data.SqlClient.SqlException,Message=Conversion failed when converting date and/or time from character string.,Source=.Net SqlClient Data Provider,SqlErrorNumber=241,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=241,State=1,Message=Conversion failed when converting date and/or time from character string.,},],'
Source

Answer 1

If possible, specify datetime2 instead of datetime in your CONVERT function.