In this post I will cover methods to get the start and end for the current, previous and next week using an Azure Data Factory Pipeline expression. I have a few pipelines which only run once a week that need to pull data from a source for the current or previous weeks, there are as always many ways to achieve this, but these are the expressions I tend to use.
This expressions should also working Azure Synapse.
The Current Week
Start of the Week (Sunday)
@formatDateTime(
subtractFromTime(
utcNow(),
dayOfWeek(utcNow()),
'Day'
),
'yyyy-MM-dd'
)
So let’s explain what is going on here. We are using subtractFromTime to subtract the current day of the week (number) as a number of days from the current time (utcNow).
ADF starts the week on a Sunday (Day 0), so if today is Friday the day of week number is 5. Today minus 5 days takes us back to the start of the week Sunday.
subtractFromTime is wrapped with formatDateTime so we can specify what format we want returned yyyy-MM-dd in this example. But you could use any format or specify an exact time i.e. yyyy-MM-dd 03:00:00
Start of the Working Week (Monday)
@formatDateTime(
subtractFromTime(
utcNow(),
sub(dayOfWeek(utcNow()),1),
'Day'
),
'yyyy-MM-dd 03:00:00'
)
Virtually the same as the previous example except now we have sub() function around dayOfWeek. If today is Friday Day 5 we are using the Sub function to subtract 1.
Today minus 4 days takes us back to Day 1 or Monday
End of the Week (Saturday)
@formatDateTime(
addDays(
subtractFromTime(
utcNow(),
dayOfWeek(utcNow()),
'Day'
),
6),
'yyyy-MM-dd 03:00:00'
)
As above we are getting the start of the week and adding 6 days to get the last sat of the week. We add 6 rather than 7 as ADF day number start at 0 Sunday to 6 Saturday.
End of the Working Week (Friday)
@formatDateTime(
addDays(
subtractFromTime(
utcNow(),
dayOfWeek(utcNow()),
'Day'
),
5),
'yyyy-MM-dd 03:00:00'
)
The Previous Week
Start of the Previous Week (Sunday)
@formatDateTime(
subtractFromTime(
utcNow(),
add(dayOfWeek(utcNow()),7),
'Day'
),
'yyyy-MM-dd'
)
Start of the Previous Working Week (Monday)
@formatDateTime(
subtractFromTime(
utcNow(),
add(dayOfWeek(utcNow()),6),
'Day'
),
'yyyy-MM-dd'
)
End of the Previous Week (Saturday)
@formatDateTime(
addDays(
subtractFromTime(
utcNow(),
dayOfWeek(utcNow()),
'Day'
),
-1),
'yyyy-MM-dd 03:00:00'
)
End of the Previous Working Week (Friday)
@formatDateTime(
addDays(
subtractFromTime(
utcNow(),
dayOfWeek(utcNow()),
'Day'
),
-2),
'yyyy-MM-dd 03:00:00'
)
The Next Week
Start of the Next Week (Sunday)
@formatDateTime(
subtractFromTime(
utcNow(),
sub(dayOfWeek(utcNow()),7),
'Day'
),
'yyyy-MM-dd'
)
Start of the Next Working Week (Monday)
@formatDateTime(
subtractFromTime(
utcNow(),
sub(dayOfWeek(utcNow()),8),
'Day'
),
'yyyy-MM-dd'
)
End of the Next Week (Saturday)
@formatDateTime(
addDays(
subtractFromTime(
utcNow(),
dayOfWeek(utcNow()),
'Day'
),
13),
'yyyy-MM-dd 03:00:00'
)
End of the Next Working Week (Friday)
@formatDateTime(
addDays(
subtractFromTime(
utcNow(),
dayOfWeek(utcNow()),
'Day'
),
12),
'yyyy-MM-dd 03:00:00'
)