ron.grace 36037 (3/3/2015)
A few attempts to get the right syntax, but got there eventually, many thanks@Dest1 + (DT_STR, 4 , 1252)DATEPART( "year" , GETDATE() ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART( "month" , GETDATE() ) , 2 ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART( "day" , dateadd("day",-1,getdate()) ) , 2 ) + ".xls"
You're only substracting on the day part which means that on April 1st, you'll get 20150400 (speaking of April's fool). And on New Year, you'll get 20160000.
To avoid that, you need to include the date calculation in every datepart. To make it easier, you can create a variable to store the correct date.
I'll do it slightly different.
@[User::Dest1]
+ (DT_WSTR, 8) ((DATEPART( "year" , @[User::PrevDay]) * 10000)
+ (DATEPART( "month" , @[User::PrevDay] ) *100 )
+ DATEPART( "day" , @[User::PrevDay]) ) + ".xls"