SSIS expression previous date without DateAdd()

Question

Currently developing a package that passes an expression from a previous date to a filename. The current code I have is the following as a string variable:

(DT_WSTR,20)DATEPART("YYYY",Dateadd("DD",-1,dateadd("MM",datediff("MM", (DT_DATE) "1900-01-01",getdate())-2, (DT_DATE) "1900-01-01"))) + RIGHT("0"+(DT_WSTR,20)DATEPART("MM",Dateadd("DD",-1,dateadd("MM",datediff("MM", (DT_DATE) "1900-01-01",getdate())-5, (DT_DATE) "1900-01-01"))),2) + "01"

This currently produces the output of:

20171101

This is currently incorrect because I'd like the date to be from the previous year:

20161101

Here's the forumula I'd like:

Return the 1st day of the month that is 7 months in the past from today's date.

Example: 5/2/2017 would return 11/1/2017; 6/21/2017 would return 12/1/2016; 7/10/2017 would return 1/1/2017; etc.

Is this possible to do via a variable in SSIS?

Answer 1

Your expression can be modified (and simplified) to this

(DT_WSTR, 8)( ( YEAR( DATEADD( "MM", -7, GETDATE() ) ) * 10000 ) + ( MONTH( DATEADD("MM", -7, GETDATE() ) ) * 100 ) + 1 )

• subtract 7 months from current date
• multiply resulting year by 10000
• subtract 7 months from current date
• multiply resulting month by 100
• add year-value, month-value and 1 (first day)
• convert to string

---

Credit to @Rangani in Yesterday's date in SSIS package setting in variable through expression for "multiply and add instead of string concat" trick