SSIS: Get Date for Day of the Week

Question

In SSIS, how do I get the date for a specific day of the week? For example, how do I get last Sunday's date?

I assume I have to do some subtraction on the day-of-week, but not sure:

DATEADD("d", -DATEPART("DW", GETDATE()), GETDATE())

Answer 1

You can use the following code to get the date for any given day in the current week:

DECLARE @DesiredDay INT = 0;
DECLARE @OutputDate DATE;
SET DATEFIRST 1; /* Monday is day 1 */
SET @OutputDate = DATEADD(d, @DesiredDay - DATEPART(DW, GETDATE()), GETDATE());
SELECT @OutputDate;

I'm setting @DesiredDay to 0 since you requested Sunday in your question. For Monday, set this to 1, for Tuesday set it to 2, etc.