Check if a timestamp variable is between 2 timestamp variables.

[#1]

In this scenario, I need to check if the execution time is between 5:00AM-5:15AM every first day of each month. I'm having trouble with the timestamp format and it is giving me script error during execution. Can you help me find out what is wrong ?

LET vNow= timestamp(Now(),'M/D/YYYY h:mm:ss TT') ;
LET vStartTime = timestamp(monthstart(Today()),'M/D/YYYY 5:00:00 TT');
LET vEndTime = timestamp(monthstart(Today()),'M/D/YYYY 5:15:00 TT');


IF $(vNow) >= $(vStartTime) and
 $(vNow) <= $(vEndTime) THEN
 SET vFirstRun = 1;
ELSE
 SET vFirstRun = 0;
END IF;

[johnw]

The $() is doing dollar sign expansion, which is to say it's directly inserting the dates and times into your script. All you want to do is compare the variables themselves. Also, the IF and THEN need to be on the same line, I believe. So this:

LET vNow= timestamp(Now(),'M/D/YYYY h:mm:ss TT') ;
LET vStartTime = timestamp(monthstart(Today()),'M/D/YYYY 5:00:00 TT');
LET vEndTime = timestamp(monthstart(Today()),'M/D/YYYY 5:15:00 TT');

IF vNow >= vStartTime and vNow <= vEndTime THEN
SET vFirstRun = 1;
ELSE
SET vFirstRun = 0;
END IF;

[#3]

Thanks for the response.

I changed my logic as per suggestion and didn't get any errors. Great !

I tested simply by updating the vNow as timestamp(monthstart(Today()),'M/D/YYYY 4:00:00 TT').

Since 4:00AM is not between 5 and 5:15 AM, I should get back a result of vFirstRun = 0. However, I got vFirstRun = 1 instead.

It looks like the "and" doesn't work and just checks if either of the expression satisfies.

[johnw · Accepted solution]

Ah, I missed that the first time. Your variable expressions are only changing the FORMAT of the timestamps. They are NOT changing the underlying values. You need to actually modify the values themselves, and the format is completely irrelevant for your comparison, which will be done numerically. So:

LET vNow = now();
LET vStartTime = monthstart(today())+5/24;
LET vEndTime = monthstart(today())+5.25/24;

IF vNow >= vStartTime and vNow <= vEndTime THEN
SET vFirstRun = 1;
ELSE
SET vFirstRun = 0;
END IF;

And to test a matching condition:

LET vNow = monthstart(today())+5.1/24;

And to test a non-matching condition:

LET vNow = monthstart(today())+4/24;

[#5]

Thanks John. It worked beautifully !