如何获得ADF2中动态内容的一个月的最后一天? [英] How can I get the last day of a month in dynamic content in ADF2?

问题描述

我想根据utcnow()时间戳获取一个月的最后一天.

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

应该在下面的表达式中自动代替月份中的"dd":28、30或31:

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')}

考虑到实际上是八月，我希望从表达式中得出以下结果: "2019-08-31T23:59:59.999"

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

推荐答案

我建议最简单的方法是将日期及其相应的月末日期存储在表或文件中(例如，在Data Lake或Blob存储中) )，然后仅使用Lookup任务即可获取日期.这将是最容易维护和调试的.

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.

如果您的管道中有一些计算，为什么不将其传递到无疑会更容易的地方.例如，如果您有SQL数据库，请在日历表中存储一个预先计算的月末日期列.然后，就像进行查找一样简单. SQL Server和Azure SQL DB甚至都内置了功能，因此您不必编写自己的函数:

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() )

那有多容易?如果您使用的是Databricks，有一个简单的示例，此处

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

如果您绝对必须使用内置函数和表达式在Data Factory中内联，那么在技术上是可行的，但我首先要通过测试来做一些事情:

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)

这种更简单的方法(请参见有关较旧的更复杂版本的编辑)将日期设置为28，然后加上5天(始终在下个月登陆)，然后将日期重置为1，然后请假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.

已成功测试了2000年1月1日至2040年12月31日的日期.

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

我建议您使用一个变量(例如variables('inputDate'))，以便您可以对其进行测试，但是在完成测试后，可以将variables('inputDate')替换为utcnow().

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.

这篇关于如何获得ADF2中动态内容的一个月的最后一天?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！