Variable Question

Toad Data Point & Toad Intelligence Central
1

This ties back to the automation thread I had yesterday, but it's something new. So far everything works perfectly, except for an issue with the way minutes are handled.

These are my variables:

Variable 1
Name: Date
Value: Date('yyyyMMdd')

  	Variable 2
  	 Name: Time
  	 Value: DatePart("h", Now) & DatePart("n", Now)

These variables are used as part of the Output file name:

Tech Progress_#Date#_#Time#.xlsx

The issue that we're seeing is that when the report runs at the top of the hour, which it's setup to do, the hour is captured in 24HR format (ie. 14 for 2pm), but the minutes are captured as a single digit - this is the file name generated at the 2:00 PM (1400) report:

Tech Progress_20101214_140.xlsx

When what we really want is:

Tech Progress_20101214_1400.xlsx

I understand it's a fairly trivial difference, but these reports are used for auditing purposes, and any discrepancy could be taken the wrong way. So, is there anyway to force the time to pad zeros?

2

Apparently, I solved my own question - here’s how, incase anyone else has run into this:

This same zero-pad issue will occur with single digit months and days, so I made a change to Variable 1 as well to account for that

Variable 1
Name: Date
Value: Format(Date(‘yyyyMMdd’),“yyyymmdd”)

Variable 2
Name: Time
Value: Right(“0” & Hour(Now),2) & Right(“0” & Minute(Now),2)

Both test and render correctly in the expression builder.

3

Good solution.

Thanks.

4

I tried to use Format but I got an error message and the function was not listed in the available functions for the automation variable. I am using version 2.6.1.568 what version are you on?

5

The variable expression builder uses VB functions. I don’t think format is one of those. Try FormatDateTime. Here are list of functions.

http://www.w3schools.com/vbscript/vbscript_ref_functions.asp

Debbie

6

Greg,

I’m using 2.7.0.348. I’m finding that the expression builder supports a wider variety of functions than are explicitly listed - this is a good thing (for me, any way), as I wouldn’t be able to use automation otherwise.

GregDavis11009 wrote:

I tried to use Format but I got an error message and the function was not listed in the available functions for the automation variable. I am using version 2.6.1.568 what version are you on?