根据提供的日期参数减去天数 [英] Subtract number of days based on provided date parameter

问题描述

我在数据工厂中创建了一个管道，我想从源中检索当月和上个月的数据.当我运行管道时，请提供名为ExtractDate的所需参数.格式为MM/DD/YYYY. 在当月，我在设置变量"活动中使用了以下表达式:

I created a pipeline in data factory and I want to retrieve data from a source for the current month and for the previous month. When I run the pipeline I give the needed parameter named ExtractDate. The format is MM/DD/YYYY . For the current month I used the following expression in 'Set Variable' activity:

@replace(item().Query,'EXTRACTDATE',formatDateTime(variables('ExtractDate'), 'yyyyMM'))

在上个月，我尝试过:

@adddays(variables('ExtractDate'),-28)

例如，当用户在运行管道时设置日期为07/31/2019时，将出现问题.然后，上个月仍然是7月.如果我将数字增加到31，则用户可能会介绍03/01/2019，从3月开始，它将跳过2月.

The problem appears when the user will set when running the pipeline the date 07/31/2019 for example. Then the previous month will still be July. And if I increase the number to 31, then there is a possibility that the user will introduce 03/01/2019 and from March it will skip the month of February.

我试图考虑一种解决方案，但不幸的是，数据工厂中没有可用的"addmonths".

I tried to think of a solution, but unfortunately there is no 'addmonths' available in data factory.

请问有什么想法吗?...我已经在此问题上花了2天的时间.

Any ideas please?...I've spent 2 days on this issue..

推荐答案

addMonths和addYears.请对此

addMonths and addYears are not supported by ADF so far.Please vote up this thread to push the progress.

我的技巧是使用 ADF中的内置功能.请查看我的测试:

My trick is use combination of bulit-in functions in ADF. Please see my test:

这个月很简单:

@concat(substring('07/16/2019',6,4),substring('07/16/2019',0,2))

输出:

上个月比较复杂，应该检查是否是一年的第一个月.

Last month is little complex.It should check if it is the first month of the year.

@if(equals(substring('07/16/2019',0,2),'01'), 
concat(
string(sub(
int(substring('07/16/2019',6,4)),1)),
'12'),
string(sub(
int(concat(substring('07/16/2019',6,4),
substring('07/16/2019',0,2))),1)
)
)

如果输入参数为01/16/2019，则输出看起来像:

if the input param is 01/16/2019,then the output looks like:

我的测试基于静态值，请用您的变量替换它.

My test is based on the static value,please replace it with your variable.

仅作总结:

最终的动态工作内容应如下所示:

The final working dynamic content should be like as below:

@if( equals(variables('SubstringMonth'),'01'), 
concat(string(sub(int(variables('SubstringYear')),1)),'12'), 
concat(variables('SubstringYear'),string(if(or(equals(sub(int(variables('SubstringMonth')),1),11),equals(sub(int(variables('SubstringMonth')),1),10)), 
sub(int(variables('SubstringMonth')),1) , 
concat('0',string(sub(int(variables('SubstringMonth')),1) )))) ))

这篇关于根据提供的日期参数减去天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！