I am trying to copy data from csv to sql db in adf copy activity but the copy activity is not converting string format to datetime format.

Question

I am copying data from CSV blob storage to Azure sql db using copy activity, two columns have string format but have data in dd-mm-yy hh:mm format, this type conversion from string to datatime in target table is producing errors, theni tried stored procedure to cast the values from string to datetime and then copy data to the target columns, please help.

i have tried converting the data type in excel itself and then upload to blob storage but still uploading as string in

Answer 1

Hi Arihant Singh Rathod ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

The error "The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value" typically occurs when the date string in your source data does not match the expected format or contains invalid date values.

Kindly verify if the column contains a valid date. Ensure that all date strings in your source data are in the correct format and that there are no invalid date values.

You can try using Dataflow to handle complex data conversions using cast transformation or derived column transformation. Try the below expression to convert the string to date :

toTimestamp(columnName, 'yyyy-MM-dd HH:mm:ss')

Hope it helps. If you have any further query, kindly let us know. Kindly accept the answer by clicking on Accept answer button. Thankyou