Azure Data Factory - calculate the number of days between two dates

Question

I have to calculate the number of days between two dates and I search and I don't find any similar function available in ADF.

what I've noticed so far is that if I want to get the number of days between 2 columns, it means that the columns must be date columns, but I have timestamp columns (date + time)

how can I transform these columns into Date columns? or do you have other idea?

Answer 1

This is the expression that I used for Data Flow.

toDate(toString({max_po create date},'yyyy-MM-dd')) - toDate(toString(max_datetimetoday,'yyyy-MM-dd'))

max_po, create date and max_datetimetoday are TimeStamp(date + time) columns.

The result is in days.

Answer 2

Using the fact that 86,400 is the number of seconds in a day

Now, using the function ticks, it returns the ticks property value for a specified timestamp. A tick is a 100-nanosecond interval.

@string(div(sub(ticks(last_date),ticks(first_date)),864000000000))

Can re-format any type timestamp using function formatDateTime()

@formatDateTime(your_time_stamp,'yyyy-MM-dd HH:mm:ss')

Example:

@string(div(sub(ticks('2022-02-23 15:58:16'),ticks('2022-01-31 15:58:16')),864000000000))