8

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"

Michael
  • 471
  • 1
  • 7
  • 13
  • 1
    I've worked up an expression for this but it's really complicated. My honest opinion is you would be better off holding the dates in a database table and doing a Lookup to get the value. You are a bit hamstrung in ADF as the date functiosn don't as yet have `addmonths`, but it's technically possible. I'm going to do some extensive testing on it first, maybe post later. – wBob Aug 23 '19 at 12:59
  • 1
    Unfortunatelly I need to use this, cause I send a query to an Elasticsearch DB and there I need (dynamically) the date range. – Michael Aug 23 '19 at 16:17
  • 1
    Hi @Michael, having had a chance to think about it, I've come up with a much simpler expression using on 4 function calls as opposed to the original 13. – wBob Aug 30 '19 at 09:45

2 Answers2

9
    @adddays(startOfMonth(addToTime(utcnow(), 1, 'Month')), - 1, 'yyyy-MM-dd')

More here:

Azure Data Factory - Expressions for Strings, Dates and More

SomeGuy
  • 223
  • 3
  • 14
3

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.

Carlos Gomez
  • 200
  • 1
  • 12
wBob
  • 13,710
  • 3
  • 20
  • 37