Search code examples
azure-data-factory

How use the conversion settings in Azure Data Factory to convert date formats

I have lookup table that does a lookup on a date field called 'as_of_date' which is used in my copy activity to copy data to the Sink. The 'as_of_date' field is in the following format yyyyMMdd.

Copy activity has the following parameters

@concat('XYZ_',
    pipeline().parameters.TableName,
    '_',
    activity('Lookup1').output.firstRow.as_of_date,
    '_',
    formatDateTime(utcNow(),'yyyyMMddhhmmss'),'.txt'
    )

enter image description here

This will send the data to the sink with the 'as_of_date' field as yyyyMMdd. Can someone help me figure out to modify the above so that the 'as_of_date' field is yyyy-MM-dd

I thought that as I have a Lookup on the as_of_date field, I thought I could parameterize it with the 'Type conversion settings' shown here with the following parameter for the lookup

enter image description here

enter image description here

Can someone let me know if it's possible?

Just to add some clarification on the question There are two places where the as_of_date can be sourced

  1. Lookup activity, see preview enter image description here
  2. Copy Activity, see preview enter image description here

As you can see the table contains an as_of_date field which is in the format yyyyMMdd at source. When it is sent to the Sink I would like it to sent with the format yyyy-MM-dd

Solution

  • If you want to Convert the yyyyMMdd format to yyyy-MM-dd. You can use your source query like below

    SELECT CONCAT(SUBSTRING(as_of_date, 1, 4), '-', SUBSTRING(as_of_date, 5, 2), '-', SUBSTRING(as_of_date, 7, 2)) AS converted_date
FROM date02;

    I have used a LOOK UP activity to query my date02 table.

    SELECT as_of_date
FROM date02

    enter image description here

    Followed by the Copy data activity and in the Source query I am using my yyyyMMdd transformation and converting the String data type to Date format yyyy-MM-dd.

    enter image description here

    In the Mapping the DateTime format is yyyy-MM-dd enter image description here

    Output: enter image description here enter image description here