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:
- Go to the Response Screen View
- In "Response Detail Entry Prospect Applet" click on Query button
- Enter Today() as criteria In "Created Date" Field
- Click on "Go" button
- 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:
- Go to Service Requests > All Service Requests or My Service Requests.
- Create 2 new service request records with default fields populated.
- Expose 'Opened' (Date Opened) and 'Closed' (Date Closed) in List Applet using Menu > Columns Displayed.
- Click on Query button and enter IS NULL (or IS NOT NULL) in 'Date Opened' in the Form applet.
- Tab out to go the next field - "Date Opened" is blanked out.
- Now hit Enter.
- 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