My final goal is to create a derived column in Azure Data Factory (in a dataflow), using 2 dates as following.

I would like to subtract a date named MinWADAT (in integer format, like '20120203') from currentDate() (returning for example '2021-10-05'). The result should be in days unit.

I try to use the following function:

toDate(MinWADAT)-currentDate()

but the result is an empty column.

Can you help me? Thank you in advance.

CodePudding user response：

You can use an expression of toInteger( (currentTimestamp() - toTimestamp(MinWADAT, 'yyyyMMdd')) / 86400000 ) to get date difference in day(s).

explanation:

1. compute the difference of 2 dates in timestamp datetype - <result 1>
2. divide <result 1> by 1000 milliseconds * 60 seconds * 60 minute * 24 hour = 86400000 to get difference in day(s) - <result 2>
3. convert <result 2> into integer by using toInteger(<result 2>)