Convert timestamp to integer in ADF dataflow

Srikanth Thota 6 Reputation points
2022-04-08T06:07:37.86+00:00

Hello All,

I need to convert timestamp(2014-06-19 00:00:00.000) to Integer(yyyyMMdd) in ADF dataflow. I have used derived column and in the expression i wrote this below

toInteger(toString(toDate('2014-06-19 00:00:00.000', 'yyyy-MM-dd HH:mm:ss'), 'yyyyMMdd'))

and its working perfectly am able to convert to integer required format but the data is taking default value which i kept in the expression i.e..,2014-06-19 00:00:00.000

when i tried to replace with column name like below :

toInteger(toString(toDate('modified', 'yyyy-MM-dd HH:mm:ss'), 'yyyyMMdd')) , am receiving null values.

Could someone help me here?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,687 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,180 questions
{count} votes

1 answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 38,401 Reputation points Microsoft Employee
    2022-04-08T09:27:26.467+00:00

    Hi @Srikanth Thota ,

    Thank you for posting query in Microsoft Q&A Platform.

    As per my understanding you are trying to take a column which contains dates and want to generate integer from it in format yyyyMMdd. Please correct me if I am wrong.

    I see that you mentioned your column name in single quotes. If you do that it comes string not a column. So you should consider removing single quotes there.

    toInteger(toString(toDate('modified', 'yyyy-MM-dd HH:mm:ss'), 'yyyyMMdd')) your expression. If you observe here column name modified is inside single quotes.

    Please check below screenshots for better idea.

    191190-image.png

    Hope this helps. Please let us know if any further queries.

    -----------------

    Please consider hitting Accept Answer. Accepted answers help community as well.