Query DateTime field using Date only in FileNet Content Engine

Question

Is it possible to query using only a Date field such as '2017-03-02' in IBM FileNet?

I have tried the below statement and it doesn't seem to work

SELECT * 
FROM Table_Name 
WHERE EstimatedDate = '2017-03-02'

OR

SELECT * 
FROM Table_Name 
WHERE EstimatedDate <= DATE '2017-03-02'

I tried including TIMESTAMP and the below query works but i want to search only by using date such as '2017-03-02'

SELECT * 
FROM Table_Name 
WHERE EstimatedDate <= TIMESTAMP '2017-03-02T00:00:00.000Z'

Answer 1

According to IBM FileNet P8, Version 5.2 - SQL Syntax reference"

<literal> ::= <string_literal> | <integer> | <float> | 
              <ISO datetime> | <W3C datetime> | TRUE | FALSE | UNKNOWN | <guid>

<ISO datetime> ::= YYYYMMDDThhmmss[,ffff]Z
<W3C datetime> ::= YYYY-MM-DD[Thh:mm:ss[.ffff]][<timezone>]

Thus, in FileNet P8 do not use timestamp or date keywords, but only the date you have written in one of these formats, and note - without an apostrophe sign!

You can find examples in a free book Developing Applications with IBM FileNet P8 APIs, for example on a page 73 there is Example 3-30 :

// Construct the sql statement
SearchSQL sql = new SearchSQL(
"select ISTOStartDate, ITSOEndDate, ITSOVehicle " +
"from ITSOIdleActivity " +
"where “ +
"ITSOVehicle = OBJECT('{D5DC8C04-2625-496f-A280-D791AFE87A73}') " +
"AND ITSOStartDate < 20090801T000000Z OR " +
"ITSOEndDate > 20090701T000000Z" );

As you see, the date in this example is written directly without any apostrophes as: 20090701T000000Z using <ISO datetime> format, you can also use another format: 2009-07-01

Answer 2

To search for a particular date you need to use a range between two timestamps: start of the target day and start of the next day. For today's date the query will be:

SELECT * 
FROM Table_Name 
WHERE EstimatedDate >= 20180420T000000Z AND EstimatedDate < 20180421T000000Z

Please note that the timestamps above assume the UTC time zone (hence 000000Z). If your task is supposed to handle time zones, the timestamps should be adjusted accordingly. For example, for Europe/Rome (current time zone offset +02:00) that would be

EstimatedDate >= 20180419T220000Z AND EstimatedDate < 20180420T220000Z