I am fetching the copy duration from copy activity and i want to write that as hh:mm:ss format in Azure sql db

Question

I have a requirement of calculating the duration of copy activity to be stored in Azure sql database and format should be hh:mm:ss in DB. I fetched activity.copyDuration and can see seconds coming out as integer. Now how can i pass that to DB to view as hh:mm:ss

I tried creating Duration column in db as time data type but it is throwing an error int or string can’t be move to time

Answer 1

Before you insert the data into the database, you need to convert the integer seconds into a time format. In a derived column, use :

toString(toTimestamp(lpad(toString(copyDuration), 6, '0'), 'HHmmss'))

If you initially tried to store the values in a column of type time and encountered errors, it could be due to the way the data was formatted or the limitations of the time data type (which typically handles up to 24 hours). Instead, consider using a varchar type for storing the time as a string, which will avoid data type conversion errors in SQL.

More links :

https://stackoverflow.com/questions/73323046/unable-to-transform-string-to-datetime-timestamp-in-specific-format-in-adf-dataf

https://learn.microsoft.com/en-us/answers/questions/1025143/cannot-convert-utc-to-timestamp-in-azure-data-fact