Toad World® Forums

Custom DATEFILE in automation designer


I have 2 apps in automation designer, these app will export the dataset in my local PC.
I want to save the file using current date. I have use %DATEFILE% format but the outcome from this format is : 2021_7_9(yyyy_mm_dd).

I want the format to be like this: 20210709 (yyyymmdd).

How can i change the format for %DATEFILE%?


One way to do that would be using a query iterator to create your own date variable. In the screenshot below, I dropped a query iterator into automation designer, renamed it to QI, and entered a query to return my variable value:

Then, drop the export dataset action off of that (notice it is a sub-node in the tree, not just a node after the query iterator. This is important). And you'll see that my variable is %QI.DATEFILE%

Then, when you want to run the export, run the Query Iterator action, not the export dataset action.

Hi John,

Thank you for your reply. I have tried using the SQL Iterator, but i found difficult to run the export dataset from sql iterator. I need to double click from SQL Iterator and click run using connection and then it is able to export my desire naming file.

My task scheduler also not function when i set from Query Iterator action.

Please advise the solution for my automation to run with expected result.


Does this work? Rt-click an query iterator, then Run?

If not, make sure that query iterator and export dataset are assigned to the same connection.
Double-click each, then look on the status bar of the properties dialog for each. It sounds like maybe they are different.

Once that is right....schedule the Query Iterator action. Not the Export Dataset action. The Query Iterator will run the Export Dataset.

Hai John,

I have check, on the step 2. When I double click from export dataset. The file display as below


My both query iterator & export dataset action are both in same connection. From schedule, I also have set from Query Iterator but somehow my schedule
Is not running.


It looks like we stumbled across a bug. Using a system variable name (see options -> variables) in a query iterator doesn't work when scheduled. Weird.

Do this:

In your query iterator, change the field name to something else (I changed it from DATEFILE to THE_DATE)

Then, in your export dataset action, change your filename accordingly... so, c:\%QI.THE_DATE%.csv or whatever.

Scheduling of the SPJ_TIPPERTRUCK_QI app should work after that.