CODEWITHSUNDEEP
datetimetimeazure-data-factory
Henri
Henri

Reputation: 15

time to datetime field in ADF copy activity gets current date

I have an ADF copy activity in an ADF pipeline. Source is a csv file ,target/sink is a Azure SQL db. Source has amongst other fields a field with time only values, e.g. 07:45:00. In the copy activity I want to insert the field in a datetime field in the target so we can easily convert it later. Normally, in SQL Server, if you add a single time value in a datetime field, SQL Server will automatically add 1/1/1900 as a date in front of it. Now however from the copy activity if the time value is copied, automatically the current date is added. Is there a way to prevent this? I prefer not to do transform data here already (except for the string time value to datetime format of course)

I tried to analyse the copy activity and the column mapping json created but I don't see anything else then string to datetime mapping. Is this default behavior of ADF copy activity?

Upvotes: 0

Views: 578

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35603

Copying a time-only value from a CSV to a datetime column in an Azure SQL database the current date is automatically added to the time value. This is the default behavior of ADF copy. Instead you could change the mapping of the source column to a string column instead. Then, in the sink, use CONVERT() to convert the string to a datetime value. e.g:

INSERT INTO mytable (datetimecolumn)
VALUES (CONVERT(datetime, '1900-01-01 ' + timestringcolumn))

Upvotes: 1

Related Questions