question

ewinkiser avatar image
0 Votes"
ewinkiser asked Viorel-1 answered

ADF Converting String to date from datetime in Copy Activity

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

86268-image.png


azure-data-factory
image.png (10.7 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ewinkiser, what's the datatype for the Prop_5 column in the Database table?

0 Votes 0 ·

Thanks @nasreen-akter

It's [nvarchar](max) NULL,

Again, I always appreciate your assistance!
Mike Kiser

0 Votes 0 ·

Hi @ewinkiser, I am adding @Viorel-1.

@Viorel-1: do you think, substring then convert would help in this scenario. Please advise. Thanks!

0 Votes 0 ·

Hi @ewinkiser,

We still have not heard back from you. Just wanted to check if any of the below suggestions were helpful? If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

Thank you

0 Votes 0 ·

1 Answer

Viorel-1 avatar image
1 Vote"
Viorel-1 answered

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you @Viorel-1. @ewinkiser: would you please try datetime2 @Viorel-1 suggested. Thanks!

0 Votes 0 ·