Hi, I am trying to find documentation for the syntax used in the expression builder of the set variable activity in the automation tab of TDA.
I have two issues. I am trying to derive a date in the MMYY fomat that is three months ago from the current date. I can get it to work by using the following, but I need a way to pull the MM format of the month or pad the string that I’m pulling.
LEFT(FormatDateTime(DateAdd(“m”,-3,Now),vbshortdate),1) & Right(FormatDateTime(DateAdd(“m”,-3,Now),vbshortdate),2) pulls 610
The second issue I have is that when I hardcode the variable to 0610 and then use it in a find and replace activity TDA is truncating the 0 and replacing the text as 610 which then breaks my sql.
Please help.
Thanks.
I found a way to handle this, and am posting my solution in case someone else finds it useful.
For the first part of the question
I am trying to derive a date in the MMYY fomat that is three months ago from the current date. I can get it to work by using the following, but I need a way to pull the MM format of the month or pad the string that I’m pulling.
LEFT(FormatDateTime(DateAdd(“m”,-3,Now),vbshortdate),1) & Right(FormatDateTime(DateAdd(“m”,-3,Now),vbshortdate),2) pulls 610
I used the following expression:
Right(“00” & Month(Date) - 3, 2) & Right(Year(DateAdd(“m”, -3, Now)),2)
For the second part of my question:
The second issue I have is that when I hardcode the variable to 0610 and then use it in a find and replace activity TDA is truncating the 0 and replacing the text as 610 which then breaks my sql.
I had to pass the variable as a string so either use Cstr or since my function actually converted my date to a string the automation script now passed the 0610 value that I wanted.
-Greg
Sorry I didn’t respond earlier (busy day )
The variable expressions are is VB Script as you found out.
The leading zero issue should be fixed in the 2.7 code. Casting to string is the correct work around.
Debbie