My Oracle Support Banner

Query In DATETIME Type Fields Using Today() Or Is Null / Is Not Null Not Fetching Correct Records (Doc ID 2687020.1)

Last updated on OCTOBER 13, 2021

Applies to:

Siebel Call Center - Version 20.1 to 20.6 [Release V17]
Information in this document applies to any platform.


Between Siebel 20.1 and 20.6, queries in a "Date" type fields using the system function Today() or using "Is Null / Is Not Null" are fetching incorrect records

The function Today() can be used to fetch all the records created today for example. However the function is fetching all other records

Is Null / Is Not Null should fetch all records that are either null or not null in the database. However, it also fetches all other records

The issue can be reproduced at will with the following steps:

Using Today()

  1. Go to the Response Screen View

  2. In "Response Detail Entry Prospect Applet" click on Query button

  3. Enter Today() as criteria In "Created Date" Field

  4. Click on "Go" button

  5. Expected to fetch all the records of today's date, however the system fetches all records irrespective of date



  1. Go to Service Requests > All Service Requests or My Service Requests.

  2. Create 2 new service request records with default fields populated.

  3. Expose 'Opened' (Date Opened) and 'Closed' (Date Closed) in List Applet using Menu > Columns Displayed.

  4. Click on Query button and enter IS NULL (or IS NOT NULL) in 'Date Opened' in the Form applet.

  5. Tab out to go the next field - "Date Opened" is blanked out.

  6. Now hit Enter.

  7. Expect 0 rows returned, but 2 rows returned instead




To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.