I defined a date variable using the "Set Variable" option under automation, however, when I try to use the DateAdd (date function) it returns the current day.
This is what I have under Value
DateAdd('dd', -10,Date('dd-MMM-yyyy')) = Current Day
Could any one help with this? My ultimate goal is set this up to day -3 to run every Monday.
If you use the Now - 10 it will give you the date 10 days ago but not in the format you want. Below are other ways of generating a date 10 days ago. Do you need the date in a variable? Or can you generate the date in your query SQL?
Now - 10 --> 4/4/2010 12:06:58 PM
FormatDateTime(Now-10,vbShortDate) --> 4/4/2010
DateAdd(“d”, -10, Now()) --> 4/4/2010 12:06:58 PM
Date(‘dd-MMM-yyyy’) --> 14-Apr-2010 (current date)
Unfortunately, the only way I see to get the date you need is to bounce it off of a database and set your variable with that value. For oracle you would execute something like this --> select to_char( sysdate - 10, ‘DD-MON-YYYY’) mydate from dual. This would have to be in a SQL file and executed using the Execute Script activity. Then use LoopDataset to retrieve and set the ‘mydate’ value. I really do need to make this easier. If you want to go this route and need a sample of how to do this, let me know.
Debbie
I do need the date in a variable because I am querying off an “as of date” so on Monday’s the date would be Friday and Tues-Friday would be the previous business day. I was thining the if logic in the automation would allow me to define the variable depending on the day of the week. If I can’t get the variable in the right format this obviously won’t be the best method. Would you be able to provide the example you mentioned earlier? Perhaps that would be a better option?
I’ll be the first to admit this is a really long winded way of setting a date into a variable. I will be changing this for the next release but this can be used for now.
I have attached a sample Automation script which should work if you expand the zip file into your C:\Temp dir. (Use TDA 2.6)
I defined two variables. MyDate for the formated date string and DayOfWeek to provide the number day of the week so you could determine if it is a week day(2 - 6).
I am connecting to the Toad Sample Access database that is installed with the product to bounce the date and get the values I want. You can use a different database and SQL to do this.
I have to use the ExecuteScript activity to execute the statement and put the result into a result variable. Even though there is only one row returned I have to use the LoopDataset activitiy as this the only companion activity that reads cells from the result set.
The SQL I am using is below which takes the current date and subtracts 10. The first column is the date format you posted earlier and the second is the numerical day of the week.
select Format(date() - 10, ‘dd-MMM-yyyy’) as FullDay, WeekDay(date()-10) as DayOfWeek
Below is the logging which shows this sequence. I also added a comment at the bottom that displays the content of both variables.
You may have to do something more complicated to update the date that is used in the SQL. You could still use the Toad Sample database and query but use a Find and Replace to ‘stick’ in the date.
P.S. You need to use this script with TDA 2.6. There were several changes for date string handlings that require you to use 2.6.
Build started
Build Completed
Variable “MyDate” set to “EMPTY”
Variable value = EMPTY
Variable “DayOfWeek” set to “0”
Variable value = 0
Connecting to Toad Sample Database
Connection successful.
Executed Statement
Query returned 1 rows
Script execution complete
Begin loop dataset
Executing activity Loop_row_1 using row 1
Variable “MyDate” set to “#RC.FullDay#”
Variable value = 05-Apr-2010
Variable “DayOfWeek” set to “#RC.DayOfWeek#”
No more rows to process
End while loop
MyDate = 05-Apr-2010, Day of Week = 2
Done
SetDateVars.zip (1.72 KB)
Thank you works great
