Please tell me about the date type.

Question

I want to set the date to yyyy-mm-dd.

In the middle of the date, 2023-03-06T00:00:00.0000.... The date is in the form of "2023-03-06T00:00:00.0000....

The settings in the dedicted table are also date, but they are stored in the above form.

When processed in the data flow, it is in the form yyyy-mm-dd.

How can I change it to yyyy-mm-dd?

Answer 1

o change the date format from "yyyy-mm-ddThh:mm:ss.0000" to "yyyy-mm-dd", you can use a Derived Column transformation in your Data Flow in Azure Data Factory.

Assuming the input date is stored in a column called "inputDate", you can use the following expression in the Derived Column transformation:

substring(inputDate, 1, 10)

This expression extracts the first 10 characters from the inputDate column, which corresponds to the "yyyy-mm-dd" part of the date string.

Once you've added the Derived Column transformation with this expression, you can map the output column to the target column in your dedicated table that stores dates in the "yyyy-mm-dd" format.

I hope this helps

Answer 2

If the date format is still not being saved correctly in the dedicated table, it's possible that the data type of the column in the dedicated table is not set correctly.

Make sure that the data type of the column in the dedicated table is set to "date" or "datetime", depending on the specific format that you need to use. If the data type is set to something else, such as "varchar" or "nvarchar", the date formatting may not be preserved correctly.

Additionally, make sure that the output of the derived column transformation is set to the correct data type as well. You can do this by clicking on the output column in the transformation and selecting the appropriate data type from the "Data type" dropdown.

If the data type of the output column is not set correctly, the date formatting may be lost when the data is written to the dedicated table.

pls accept question