Getting the Last Day of the Previous Week SSIS Expression

Question

Could you please help me to find the last day (Sunday) of previous week? I need to convert it to string because I need to have file name like "Test_2023_01_07".

I need to do it using an expression. Thank you in advance

Answer 1

This is based on a US locale and there may be regional variations.

Solution is also based on Andriy's excellent answer, HT to Brad.

In SSIS expression language, we don't have the equivalent of @@DATEFIRST so this is hard coded to Sunday being 1. You can test this by adding an Expression to a variable DATEPART( "weekday", (DT_DATE) "2024-01-07" )

I broke this down to several Variables as I wanted to ensure I was translating the answer correctly. It can be done in a single shot but I like to show my work.

• StartDate DateTime Value of 2024-01-10 Hardcoded to start with and eventually we'll use an Expression to get current date
• CurrentWeekDayNumber Int32 Expression DATEPART("weekday", @[User::StartDate]) This will evaluate to 4
• DateOffset Int32 Expression -1 - ( @[User::CurrentWeekDayNumber] +5) %7 This will evaluate to 4
• LastSunday DateTime Expression DATEADD("DAY", @[User::DateOffset] , @[User::StartDate]) This will evaluate to 2024-01-07

Modify the StartDate to any boundary conditions you want to check and then close and re-open the package. Everything should re-evaluate.

If you're happy with how that's working and you won't need to debug, you can write all of that as a single expression

• LastSundayInOne DateTime Expression DATEADD("DAY", (-1 - ( @[User::CurrentWeekDayNumber] +5) %7), @[User::StartDate])

The final step is to tie the StartDate to a real point in time. Many people reach for GETDATE() and I always advise against it. Every time you reference getdate, the value changes. Say your process starts today at 23:59:59. The first time you look at the StartDate variable it will say 2024-01-10. The package runs for 2 minutes. The final evaluation of StartDate will be 2024-01-11 because we ticked over the day mark. Maybe that will be fine, maybe not.

Instead, I advocate that people use @[System::StartDate] That variable is the time the package starts executing but it remains constant throughout package execution.

Finally, you want to make a date string using the yyyy_mm_dd,aka ccyy_mm_dd, pattern. That is going to use the pattern of

1. Get the numeric part of the date
2. Convert to string
3. Prepend a leading zero for month/day
4. Extract the final 2 characters
5. Concatenate those parts using the underscore

Variable: FileDate Data type String Expression is

(DT_WSTR,4)(year(@[User::LastSunday])) 
+ "-" 
+ RIGHT("0"+(DT_WSTR,2)(month(@[User::LastSunday])),2)
+ "-" 
+ RIGHT("0"+(DT_WSTR,2)(day(@[User::LastSunday])),2)

That evaluates to 2024-01-07