azure-data-factory

How can I get the last day of a month in dynamic content in ADF2?

I want to get the last day of a month based on the utcnow() timestamp.

Instead of "dd" in the expression bellow there should be automatically the last day of the month (28, 30 or 31):

@{formatDateTime(adddays(utcnow(),-2), 'yyyy-MM-ddT23:59:59.999')}

Thinking that it´s actually august I expect the following result out of the expression: "2019-08-31T23:59:59.999"

Solution

  • I would recommend the simplest way to do this is store the dates and their respective end of month dates in a table or file (eg in Data Lake or Blob Store) and then just use a Lookup task to get the date. This would be the easiest to maintain and debug.

    If you have some compute in your pipeline, why not pass it off to that where it will be undoubtedly easier. eg if you have a SQL Database, store a calendar table with a pre-calculated end-of-month date column. Then it's as simple as doing a lookup. SQL Server and Azure SQL DB even have a function built in so you don't have to write your own:

    SELECT EOMONTH ( GETDATE() )

    How easy is that? If you're using Databricks there is a simple example here.

    If you absolutely have to do it inline in Data Factory using built-in functions and expression, then it's technically possible, but I'd do some through testing first:

    @addDays(formatDateTime(addDays(formatDateTime(variables('inputDate'), 'yyyy-MM-28'),5), 'yyyy-MM-01T23:59:59.999'),-1)

    This simpler approach (see edits for older more complex versions ) artificially sets the day to 28, then adds 5 days (which always lands you in the next month ), then resets the day to 1 and then takes 1 day off.

    This has been tested successfully for dates 1 Jan 2000 to 31 Dec 2040.

    I'd advise using a variable ( eg variables('inputDate') ), so you can test it but you can replace variables('inputDate') with utcnow() when you're finished testing.