Conditional expressions on a timestamp not working properly

dev support
1

I’m trying to solve a problem with finding out whether a timestamp (now) is between 2 other timestamps. The full description is here: [SOLVED] SQLS - how can I find out if getDate() is between 2 time(7) timestamps? - SQL Server Forum

The SQL solution still isn’t working properly so I’m trying to solve it programmatically in lucee now and I’m not getting the results I expect. I’m storing the timestamps as time(7) in sql server and using this code:

Timestamps: time_on: {ts ‘1970-01-01 02:00:00’} event: {ts ‘1970-01-01 11:42:51’} time_off: {ts ‘1970-01-01 14:00:00’}

Alert = False;

IF(time_on < time_off) {
    //Use this test to determine if you send an alert
    IF(time_on < event AND event < time_off)
    Alert = True;
} else {
    //Use this test when time_on > time_off and take the opposite action
    IF((time_off > event OR time_on < event)) {
        Alert = True;
    }
}

This code outputs False instead of true with these timestamps which doesn’t make sense. I’ve also tried using timeFormat() on all of the timestamps because when time_on is before midnight, (when the range crosses midnight), the date portion of the timestamp will/should confuse the engine. It didn’t help, but it’s something that should be taken into consideration.

Alert = False;

IF(timeformat(time_on,"hh:mm:ss") < timeformat(time_off,"hh:mm:ss")) {
    //Use this test to determine if you send an alert
    IF(timeformat(time_on,"hh:mm:ss") < timeformat(event,"hh:mm:ss") AND timeformat(event,"hh:mm:ss") < timeformat(time_off,"hh:mm:ss"))
    Alert = True;
} else {
    //Use this test when time_on > time_off and take the opposite action
    IF(timeformat(time_off,"hh:mm:ss") > timeformat(event,"hh:mm:ss") OR timeformat(time_on,"hh:mm:ss") < timeformat(event,"hh:mm:ss")) {
        Alert = True;
    }
}

Thanks in advance for any advice you can give me.

2

You can use dateCompare (or dateDiff) to determine differences in date/time, for example:

Alert = False;

if( dateCompare( time_on, time_off ) lt 0 ) {
    //Use this test to determine if you send an alert
    if( dateCompare( time_on, event ) lt 0 AND dateCompare( event, time_off ) lt 0 ) {
	    Alert = True;
    }
} else {
    //Use this test when time_on > time_off and take the opposite action
    if( ( dateCompare( time_off, event ) gt 0 OR dateCompare( time_on, event ) gt 0 ) ) {
        Alert = True;
    }
}

HTH

– Denny

1 Like