May 12, 2017 at 7:52 am
I need to extract data from a table for the last 45 days. I have set up 2 variables in my package, Today and Date45days earlier. The date needs to look like 20170512.
I have successfully set Today using:
RIGHT((DT_WSTR, 4) DATEPART("yyyy", GetDate()), 4) + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()), 2).
How can I populate Date45DaysEarlier using today's date formatted as 20170512 - 45 days?
May 12, 2017 at 8:04 am
joe.wolfe - Friday, May 12, 2017 7:52 AMI need to extract data from a table for the last 45 days. I have set up 2 variables in my package, Today and Date45days earlier. The date needs to look like 20170512.
I have successfully set Today using:
RIGHT((DT_WSTR, 4) DATEPART("yyyy", GetDate()), 4) + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()), 2).
How can I populate Date45DaysEarlier using today's date formatted as 20170512 - 45 days?
If you create a second variable that looks similar to the above, but replacing GETDATE() with
DATEADD( "d", -45, GETDATE() )
does it work?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 12, 2017 at 8:05 am
Exactly as you did for today's date, but replace GetDate() with an expression using date arithmetic to subtract 45 days. I don't know the syntax off the top of my head, but if it's anything like T-SQL, it'll be something like DATEDIFF(day,-45,GetDate()). Why do you need the variable to be in that format, incidentally? You'd probably find it easier to treat dates as dates, and only convert them to strings or whatever when you need to display them.
John
May 12, 2017 at 8:19 am
Unfortunately, I am building an extract to send to a third party, so the date in the table is in that format and is a string. The 3rd variable seems to at least get me the date I need, but how do I refer to the variable in the original expression:
RIGHT((DT_WSTR, 4) DATEPART("yyyy", GetDate()), 4) + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()), 2).
I have tried user: and @.
I have never tried to do anything like this in an expression.
Thank you so much for taking the time to reply!
May 12, 2017 at 8:31 am
I'm not sure I understand your question, I'm afraid. Where in the package are you using the expression(s)? When referring in an expression to a variable, you do so like this: @[User::MyVariable].
John
May 12, 2017 at 11:31 am
Thank you, I got it to work. To all of you who took time to reply and help, Thank you so much!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply