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:


1 Answer 1


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

DECLARE @DesiredDay INT = 0;
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.

  • I'm after the SSIS expression please Max.
    – PeterX
    Feb 10, 2014 at 0:34
  • In SSIS, you could obtain the date for Sunday using : DATEADD("d", 0 - DATEPART("dw", GETDATE()), GETDATE())
    – Hannah Vernon
    Feb 10, 2014 at 4:49
  • 1
    Yes, that's what I was thinking - adjust the "0" value the day-of-the-week you require.
    – PeterX
    Feb 11, 2014 at 6:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.