Open topic with navigation

About Operators to Use with Date Fields

This topic describes the operators that can be used to manipulate values in date fields.

DetailsClosed

When you enter anything in the Criteria cell or the Or cell, the is equal to (=) operator is assumed at the beginning of the text unless a different operator is specified. For example, if you enter (#:D '2000-03-10') in the Criteria cell of a date field, GE Digital APM will assume that you want to return records where the value in that field is equal to March 10, 2000.

It is not necessary to enter the = operator at the beginning of the expression in a given cell. If you do not enter the operator, GE Digital APM will insert it automatically, but it will not be displayed. In addition, GE Digital APM inserts but does not display the text WHERE or HAVING before the beginning operator (either an implied = operator or an explicit alternate operator). This means that the SQL code will contain either a WHERE clause or a HAVING clause as a result of the text that you enter in the Criteria cell or the Or cell.

Available Operators for Date FieldsClosed

The following table provides examples of operators that you can use to manipulate values returned in date fields.

Purpose Operator Example Outcome of Example
Return records that have an exact date stored in a field. = =(#:D '2000-03-10') Returns records where the date is March 10, 2000 and the time is any time.
Return records that have an exact date and time stored in a field. = =(# :dt '2006-01-01 17:00:00') Returns records where the date is January 1, 2006 and the time is 5:00 P.M.
Return records that have a date greater than the specified date in a given field. > >(#:D '2000-03-10') Returns records where the date is after March 10, 2000.
Return records that have a date less than the specified date in a given field. < <(#:D '2000-03-10') Returns records where the date is before March 10, 2000.
Return records that have a date greater than or equal to the specified date in a given field. >= >=(#:D '2000-01-10') Returns records where the date is on or after March 10, 2000.
Return records that have a date less than or equal to the specified date in a given field. <= <=(#:D '2000-03-10') Returns records where the date is on or before March 10, 2000.
Return records that do not have the specified date in a given field.

Not

!=

Not (#:D '2000-03-10')

!= (#:D '2000-03-10')

Returns records where the date is not March 10, 2000.
Return records that have multiple dates in a given field. And (#:D '2000-03-10') And (#:D '2003-06-20') Returns records where the dates are March 10, 2000, and June 20, 2003 for a specified field.
Return records that have one date or another. Or (#:D '2000-03-10') Or (#:D '2000-03-11') Returns records where the date is March 10, 2000, or March 11, 2000.
Return records that contain any values in a given field. Is Not Null Is Not Null Returns records where the field contains any date.
Return records that do not contain any values in a given field. Is Null Is Null Returns records where the field is empty.
Return records that have a date that falls within a certain range with respect to the current date.

Now()+

Now()-365

Now()-365 Returns records where the date is one year before the current date. The numeric value can be any number of days before or after the current date (see note).
Return records falling within a defined range of dates. BETWEEN BETWEEN (#:D '2000-03-10') And (#:D '2003-06-20') Returns records whose dates fall within the defined range.

Note: If you use an expression to return records where the date is a certain number of days before or after some date, even if you do not specify a time along with the date, GE Digital APM will assume that you want to return records where the time is exactly 12:00:00 A.M. on that date. For example, if you enter the expression (#:D '1997-01-05') +1, the query will return records where the date is January 06, 1997, and the time is exactly 12:00:00 A.M.

Related Information

Copyright © 2018 General Electric Company. All rights reserved.