4

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?

A. Taufique
  • 43
  • 1
  • 3
  • Should be easy to alter your existing expression - just `DATEADD` -1 month on the year and month part and hardcode `1` for the day part. – Filburt May 02 '17 at 12:37
  • 1
    Your first example seems wrong - did you mean 5/2/2017 would return 11/1/201**6**? – Filburt May 02 '17 at 12:40
  • The string variable above produces a date that is 7 months in the past, however the year is still the current year. I'm trying to have it produce a true date 7 months in the past. – A. Taufique May 02 '17 at 12:42
  • If being **7** months in the past, shouldn't your results be 5/2/2017 **->** 10/1/2016, 6/21/2017 **->** 11/1/2017 and 7/10/2017 **->** 12/1/2016? – Filburt May 02 '17 at 16:38

2 Answers2

4

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

Community
  • 1
  • 1
Filburt
  • 17,626
  • 12
  • 64
  • 115
0
SELECT LEFT(CONVERT(VARCHAR, DATEADD("MM", -6, '2017-05-02'), 112), 6) + '01'
SELECT LEFT(CONVERT(VARCHAR, DATEADD("MM", -6, '2017-06-21'), 112), 6) + '01'
SELECT LEFT(CONVERT(VARCHAR, DATEADD("MM", -6, '2017-07-10'), 112), 6) + '01'
Gordon Bell
  • 13,337
  • 3
  • 45
  • 64