Azure Synapse Pipeline Date Expression - Last Monday

Question

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.

Answer 1

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.

Result:

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.