Develop > Programming > System Language reference > List: RAD functions > RAD function: datecmp

RAD function: datecmp

A RAD function that translates the date/time fields to the correct SQL statement dialect. You can use this function in expert search of incidents, as well as in JavaScript programming.

Function

datecmp

Format 

datecmp("DateTimeField1","LogicOperator","DateTimeField2","+/-", "TimeInterval")

Parameters

This function uses the following arguments.

Argument Description Example Value (s)
DateTimeField1 A date time field in an Service Manager table. close.time
LogicOperator  A logic operator. >, >=, =, <=, <
DateTimeField2 Another date time field in the same Service Manager table. open.time
+/- Arithmetic operator: +or -. +, -
TimeInterval

A string that represents the time interval to be added to or subtracted from the second date time field.

The format of time interval can be: d, d hh:mm:ss, d h:m:s, hh:mm:ss, h:m:s, or hh:m:ss (1 digit mixed with 2 digits). Days can be omitted, or at most 9 digits. Hours, minutes, and seconds can be 1 or 2 digits (from 0 to 99), and hour:minute:second as a whole can be omitted if you enter only days.

10 02:03:04

(This string represents 10 days, 2 hours, 3 minutes and 4 seconds.)

Pay attention to the following items:

  • All arguments must be enclosed in a pair of double quotes; otherwise, the query parsing will fail.

  • This function supports AND, OR, and NOT to concatenate multiple datecmp() calls in one query.

    The following are two examples:

    datecmp("close.time", "<", "open.time","+", "1") or datecmp("close.time",">=", "open.time","+", "5:0:0")

    problem.status="Closed" and (not datecmp("close.time",">", "open.time", "+","31 04:02:30"))

  • You can combine the result of this function with other query conditions to construct a complete query. For example, you can execute one of the following queries when performing an expert search of incidents:

    • problem.status="Closed" and datecmp("close.time","<","open.time","+","04:02:30") and datecmp("close.time",">=","open.time","+","02:02:30" )
    • problem.status="Closed" and datecmp("open.time",">","close.time", "-", "04:02:30" ) and datecmp("close.time",">=","open.time","+","02:02:30" )
    • problem.status="Closed" and datecmp("close.time",">", "open.time", "+","31 04:02:30")

    Note The first two queries should return the same results, which are incidents whose closed time is between 2 hours and 4 hours from their open time; the third query should return incidents that were closed more than 31 days after their open time.

Example

An example of a JavaScript program that uses this function is as follows:

var f = new SCFile('probsummary', SCFILE_READONLY);
var query = 'problem.status="Closed" and datecmp("close.time", "<", "open.time","+", "04:02:30") 
and datecmp("close.time",">=", "open.time","+", "02:02:30")';
if (RC_SUCCESS == f.doSelect(query))
{
do
{
print(f);
}
while (RC_SUCCESS == f.getNext());
};