Siebel Tools >  Compare Dates: search spec for querying back records that were created in the last 12 hours

Compare Dates: search spec for querying back records that were created in the last 12 hours.

SOLUTION

Among other solutions, the two exposed below seem to be the most suitable in the case described here.
In both solutions the [Created] field is used. This field is an implicit system field that is automatically filled by the runtime when creating a record.
The BusComp.SetSearchSpec method is designed for setting search criteria on a specific field that is specified as first argument. If some complex query is necessary, the BusComp.SetSearchExpr method that offers entering the complete search expression may be used.

Please refer to the Bookshelf for their complete description:
- Interfaces Reference > Business Component Methods > SetSearchSpec Method
- Interfaces Reference > Business Component Methods > SetSearchExpr Method

In the eScript code, The use of the following statement for setting the search criterion (we will see below how to set the searchSpec variable) is possible:
this.SetSearchSpec("Created", searchSpec);

Please, note that these sample codes are designed to be executed in a Business Component event; the use of the 'this' pre-defined variable refers to the instance of the current object. If it is planned to be executed in an applet, the 'this' variable must be replaced by another that points to the BusComp (IE this.BusComp()).

The above statement uses a searchSpec variable that contains the search criterion. Here are two possible solutions for setting it:

1) Use the Today function
The Today() function is a standard function the user may use for performing queries in the Siebel application. It is interpreted by the runtime during the query phase. It may be used as a search criterion.

Just as you can in the UI, you can also use some arithmetic operations used in conjunction with the Today() function. For example the following statement corresponds to the last half day:

Today() - 0.5

In the eScript code, you can set the searchSpec as follows:

var searchSpec = ">= Today() - 0.5";

2) Use the eScript Date object

The eScript language offers a Date object that is actually a number of milliseconds, so if some more accurate computations must be performed on dates, this solution is preferable rather than the Today() runtime function.

The following sample code computes the date corresponding to the current date (Date creator without any parameter) from which 12 hours are deducted (12 hours * 60 minutes * 60 seconds * 1000 milliseconds = 43200000).

var currentDate = new Date();
var halfADayBefore = new Date(currentDate - 43200000);

Then, the date is formatted as the runtime is expecting (>= MM/DD/YYYY HH:MM:SS):
var searchSpec;

Clib.sprintf(searchSpec, ">= \"%d/%d/%d %02d:%02d:%02d\"",
halfADayBefore.getMonth() + 1,
halfADayBefore.getDate(),
halfADayBefore.getFullYear(),
halfADayBefore.getHours(),
halfADayBefore.getMinutes(),
halfADayBefore.getSeconds());

To summarize, the following code will perform a query on the current BusComp and fetch all records that were created in the last half of day passed:

// Solution 1
var searchSpec = ">= Today() - 0.5";

// Solution 2
var currentDate = new Date();
var halfADayBefore = new Date(currentDate - 43200000);
var searchSpec;
Clib.sprintf(searchSpec, ">= \"%d/%d/%d %02d:%02d:%02d\"",
halfADayBefore.getMonth() + 1,
halfADayBefore.getDate(),
halfADayBefore.getFullYear(),
halfADayBefore.getHours(),
halfADayBefore.getMinutes(),
halfADayBefore.getSeconds());

// Common to both solutions
this.SetSearchSpec("Created", searchSpec);
this.ExecuteQuery();
this.FirstRecord();