SSIS Date Function

2
votes

I stuck on on the date part in my ssis package

I created 3 variabels in my package & I used these expression

1) @Days - 8

2) @DateDiff -  (DT_WSTR,4)YEAR(GETDATE()) + "-"    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)+ "-"+   RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("dd", - @[User::Days] , GETDATE())), 2)

3) @datestarted - DATEDIFF("dd", (DT_DBTIMESTAMP) @[User::DiffDate],(DT_DBTIMESTAMP) @[User::PatDate]   )

Here patCon is same as getdate().

Here If i changed the Day upto 30 it will not take me to tht 2013-06-17 it will show me the same date 2013-07-17 (suppose gatedate() is 2013-07-17).

I want the expression which dynamically change the date suppose If I placed Days - 50 so it should change the month also. and If days - 500 It would change the month as well as Year.

Please let me know for the further detail.Appreciated your time and Help.I wnat to develope this only in Package side not T-SQL side.

--343

1
sqlsql-serversql-server-2008tsqlssis

1 Answers

2
votes

You're only applying your DATEADD to the portion where the day substring is taken, you need to apply it to month and year too if you want them to be affected. I believe this is it:

(DT_WSTR,4)YEAR(DATEADD("dd", - @[User::Days] , GETDATE())) + "-"    
+ RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("dd", - @[User::Days] , GETDATE())), 2) + "-"
+   RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("dd", - @[User::Days] , GETDATE())), 2)