CODEWITHSUNDEEP
sql-serverstringdatetimevariablesssis
A. Taufique
A. Taufique

Reputation: 43

SSIS expression previous date without DateAdd()

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?

Upvotes: 4

Views: 10191

Answers (2)

Gordon Bell
Gordon Bell

Reputation: 13633

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'

Upvotes: 0

Filburt
Filburt

Reputation: 18092

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

Upvotes: 4

Related Questions