Calculate next wednesday based on any given date using ssis derived column expression

Question

I am creating a package which will store a report's generation date. on basis of that date need to derive the next Wwednesday date.

Ex: report date is 11/11/2019 so wed date should be 13/11/2019.

Is the report's generation date stored in a package variable? — TrevorBrooks, Nov 18 '19 at 21:48

score 0 · Answer 1 · answered Nov 19 '19 at 05:24

This Derived Column Expression should provide the next Wednesday's date.

DATEADD("DAY",((1 + DATEDIFF("DAY",(DT_DATE)"1/1/1970",GETDATE())) / 7) * 7 + 6,(DT_DATE)"1/1/1970")

Parts (inside out).

Days since 1/1/1970 + 1.
(Divide by 7) (Implicit Cast to Int) (Multiple by 7) will round it to last Thursday.
When added days back to 1/1/1970 (+6 days more) will move to next occurring Wednesday.

Notes

Without the initial 1 day offset, If GETDATE() is a Wednesday, the result will be GETDATE()'s date.
GETDATE() can be swapped out with GETUTCDATE() or a date Variable as needed.
The +6 can be moved back to +5 if a Tuesday is desired.

1 Answers1