Visual Cron / Last Day of Month

The one scheduling software I work with is Visual Cron. This is a pretty good platform for organizing and timing the execution of different processes.

A new problem presented itself recently: Passing a specific date to a Windows batch file. For this particular program, the date had to be ‘last day of the month’ in which the program was running. Whether you’re going to try and find this date by SQL or Application functions, it’s not an easy task.

Visual cron allows you to create a custom date function, which I made use of to get the last day of this month. In order to obtain this value, the math basically states “Get the first day of next month, then subtract a day”.

In Visual Cron, create a User defined Variable [ LastDayofMonth ].

.vc_vars.PNG

The value for the variable is as follows:

{DATEADD(Days|{DATEADD(Months|{DATEFORMAT(yyyy-MM-01)}|yyyy-MM-dd|1|yyyy-MM-dd)}|yyyy-MM-dd|-1|yyyy-MM-dd)}

Using a day in November 2015 as an example:

  • This formats the date to YYYY-MM-DD (as needed by the batch file).
  • yyyy-mm-01 automatically sets to the first day of the current month [ 2015-11-01 ].
  • In order to get the first of next month, use a DATEADD of one month [ 2015-12-01 ].
  • DATEADD -1 day from that and you get the last day of this month [ 2015-11-30 ].

Now, use that variable in a scheduled process:

vc_vars2.PNG

In Visual Cron, as a part of the execute process flow, the variable (now converted to viscron-ese) is encapsulated by single-quotes to format the value that is needed in the bat file.

For testing, the .bat file is as follows:

echo %1>> lstdaymthvar.txt

After the process is run, the result is shown:

vc_vars3.PNG

I’m sure I can enhance the variable code, but for now, it works. 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s