Error: OPS7421E when using BETWEEN clause for TIMESTAMP columns in OPS/MVS RDF

Products

• OPS/MVS Event Management & Automation

Issue/Introduction

When trying to use the SELECT SQL statement with WHERE column BETWEEN 2 values as below:

ADDRESS SQL                                                              
"SELECT * FROM "Table ,                         
"WHERE datetime BETWEEN datetime1 AND datetime2"

The following errors occur:

OPS7420E SEARCH CONDITION EVALUATION ERROR ON TABLE                
OPS7421E INCOMPATIBLE DATA TYPE COMPARISON ATTEMPT                 
OPS7422E VALUE1: value  VALUE2: 'value'

Environment

OPS/MVS

Cause

The OPS/MVS SQL engine requires explicit type casting for string literals being compared to TIMESTAMP fields.

Resolution

To resolve the OPS7421E error, use the TIMESTAMP keyword before each literal and ensure the date and time are separated by a space.

Example

"WHERE column BETWEEN ",                                           
 "TIMESTAMP 'yyyy-mm-dd hh:mm:ss' AND TIMESTAMP 'yyyy-mm-dd hh:mm:ss'"  

Additional Information

For more details on supported SQL syntax and data types, see the OPS/MVS SQL Statements