copy activity puts 0's at the end of date

Question

I use copy activity in ADF to read date value from sql server and then places the data into .csv but inside the date columns in .csv file I see leading zeros. for example

source-->2024-03-14
sink in .csv shows 2024-03-14 00:00:00.0000000

Answer 1

@arkiboys

Thanks for reaching out to Microsoft Q&A.

This is happening because SQL Server is storing the date as a datetime type, which includes both date and time. When you’re copying this data into a .csv file, it’s preserving the full datetime format, including the time portion, which defaults to 00:00:00.0000000 if no time is specified.

If you want to remove the time portion during the copy activity in Azure Data Factory (ADF), you can use a data flow transformation to convert the datetime to a date type, which will remove the time portion. Here’s an example of how you might do this:

{
  "name": "Transform datetime to date",
  "type": "DerivedColumn",
  "parameters": [],
  "arguments": {
    "derivedColumns": [
      {
        "name": "date",
        "expression": {
          "value": "toDate({datetime_column})",
          "type": "Expression"
        }
      }
    ]
  }
}

Remember to use this transformed data when you’re copying to the .csv file. This should result in a .csv file with dates formatted as YYYY-MM-DD, without the time portion.

Please note that this is a high-level solutionIf you want to remove the time portion directly in the Copy Activity in Azure Data Factory, you can do so by using the format function in the mapping tab. Here’s how you can do it:

1. In your Copy Activity, go to the Mapping tab.
2. Find the column that contains your datetime data.
3. In the Derived Column section for that column, enter the following expression: format('{datetime_column}', 'yyyy-MM-dd')

Replace {datetime_column} with the name of your datetime column. This will format your datetime data as a date (without the time portion) in the YYYY-MM-DD format.
refer: https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#formatdatetime

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.