Azure Synapse管道日期表达式 – 上周一

huangapple go评论61阅读模式
英文:

Azure Synapse Pipeline Date Expression - Last Monday

问题

我有以下的Azure函数,它的作用是获取上一个星期一的日期。它的功能正常,除非当前日期是星期一。如果当前日期是星期一或星期二,我需要函数仍然获取上一个星期一的日期。这是因为时间直到星期二中午才会更新。

@{formatDateTime(
    subtractFromTime(
        utcNow(),
        sub(dayOfWeek(utcNow()),1),
        'Day'
    ),
    'yyyy-MM-dd 00:00:00'
)}

我仍在学习Azure Synapse,所以我不确定是否可以编写一个IF语句来实现这一点,或者是否有更好的编写方法。

英文:

I have the following azure function that is supposed to retrieve the date of the previous Monday. It works fine except for if the current date is a monday. I need the function to still retrieve the previous monday date if it is Monday or Tuesday. This is due to the time not being updated until middle of day tuesday.

@{formatDateTime(
subtractFromTime(
utcNow(),
sub(dayOfWeek(utcNow()),1),
'Day'
),
'yyyy-MM-dd 00:00:00'
)}

I am still learning Azure synapse so I am not sure if I can write an IF statement that accomplishes this or if there is a better way to write it.

答案1

得分: 1

我已复制上述内容并能够通过使用以下的动态内容来获得所需的结果。

@if(greater(dayOfWeek(utcnow()),1),formatDateTime(addDays(subtractFromTime(utcnow(),dayOfWeek(utcnow()),'Day'),1),'yyyy/MM/dd'),formatDateTime(addDays(subtractFromTime(utcnow(),dayOfWeek(utcnow()),'Day'),-6),'yyyy/MM/dd'))
  • 周一从上周日开始的星期几是1,所以我在检查当前日期的星期几是否大于星期一。
  • 如果大于(今天不是星期一),那么我会通过将一天添加到上周日来获得上个星期一。
  • 如果不是(今天是星期一),那么我会从上周日减去6天,这将得到前一个星期一。

这将适用于所有天,但我们需要根据所需的日期更改条件和添加/减去的天数。

Azure Synapse管道日期表达式 – 上周一

结果:

Azure Synapse管道日期表达式 – 上周一

如果希望即使当前日期是星期一或星期二,结果仍然是星期一,那么可以在上述条件中使用 or 来添加星期二的表达式。

英文:

I have reproduced the above and able to get the desired result by using the below dynamic content.

@if(greater(dayOfWeek(utcnow()),1),formatDateTime(addDays(subtractFromTime(utcnow(),dayOfWeek(utcnow()),'Day'),1),'yyyy/MM/dd'),formatDateTime(addDays(subtractFromTime(utcnow(),dayOfWeek(utcnow()),'Day'),-6),'yyyy/MM/dd'))
  • The day of week for a monday from last sunday is 1, so I am checking the current date's day of week is greater than monday or not.
  • If it is greater (Today is not a Monday), then I am giving last Monday by adding 1 day to the last Sunday.
  • If it is not, then I am subtracting -6 from the last Sunday which is the previous Monday.

This will work for all days, but we need to change the condition and the number which we are adding and subtracting as per the day we required.

Azure Synapse管道日期表达式 – 上周一

Result:

Azure Synapse管道日期表达式 – 上周一

If you want the result to be Monday even though current date is Monday or Tuesday, then give the expression for Tuesday also in the above condition using or.

huangapple
  • 本文由 发表于 2023年2月6日 05:24:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/75355619.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定