Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Check if a timestamp variable is between 2 timestamp variables.

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;


1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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;

View solution in original post

4 Replies
johnw
Champion III
Champion III

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;

Not applicable
Author

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
Champion III
Champion III

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;

Not applicable
Author

Thanks John. It worked beautifully !