Task: Identification of the previous working day to todays date.
A classic task. To load CSV data from the source file system, we need to identify the data of the previous working day.
Ie. on Thursday we download data from Wednesday and on Monday we download data from Friday last week.
It sounds simple, but you need to identify the previous business day to the current day in Azure Data Factory. And here I came across a bit, that’s the problem.
A clean solution would be to create a condition for finding date in the Add dynamic content section.
And a little dirtier, but a faster solution would be to turn to the good old t-sql.
SET DATEFIRST 1;
SELECT
cast(
format(
DATEADD(DAY,
CASE (DATEPART(WEEKDAY, GETDATE()) + @@DATEFIRST) % 7
WHEN 1 THEN -2
WHEN 2 THEN -3
ELSE
-1
END,
DATEDIFF(DAY, 0, GETDATE())), ‘yyMMdd’) as nvarchar)
as PreviousBusinessDate
In the end, one Lookup in SQL database is enough and it’s done. That is, assuming you have an SQL database. If not, you will have nothing left but “Add dynamic content”.
