333 views

Description

CURDATE() yields a "null" in SOAP encoded query when used in a WHERE clause.

1.0.7.3 Driver: Returns 0 records because the encoded query is wrong.
1.0.5 Driver: Yields a NullPointerException.

Steps to Reproduce

Within iSQL:

1. Use CURDATE() on its own to see expected behavior.
ISQL> SELECT DATEADD('dd',-10,CURDATE());
DATEADD(dd, -10, CURDATE())

2. Use CURDATE() within a WHERE clause to see unexpected behavior.
ISQL> SELECT sys_id, number FROM incident WHERE (sys_created_on >= DATEADD('dd',-10,CURDATE())) AND sys_id = '8829085b0f4eb1004f2ff45be1050e5f';
sys_id number

Rows selected = 0

Workaround

Instead of using CURDATE() inside WHERE clauses, use the DATEADD function.

For example, for a query such as following:

SELECT number, sys_created_on FROM incident WHERE sys_created_on <= CURDATE()+10;

Use:

SELECT number, sys_created_on FROM incident WHERE sys_created_on <= DATEAD 
D('dd',+10,CURDATE()); 


Related Problem: PRB623553

Seen In

Berlin Patch 8 Hot Fix 1

Fixed In

Geneva

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2015-06-16 14:39:23
Published:2015-04-08