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

Last updated on MARCH 20, 2025

Applies to:

Symptoms

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

Using IS NULL / IS NOT NULL:

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
   

Changes

Cause

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