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 ITSMA Service Management table.	close.time
LogicOperator	A logic operator.	>, >=, =, <=, <
DateTimeField2	Another date time field in the same Service Management 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());
};