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.
Variable “MyDate” set to “EMPTY”
Variable value = EMPTY
Variable “DayOfWeek” set to “0”
Variable value = 0
Connecting to Toad Sample Database
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
SetDateVars.zip (1.72 KB)