E-AD:DB2 SQL Error Executing SQL With %Datetimein. (Doc ID 2158330.1)

Last updated on JULY 06, 2016

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.55 and later
Information in this document applies to any platform.

Symptoms

On : 8.55 version, PeopleCode

DB2 SQL Error executing SQL via PeopleCode that is calculating dates.

We are encountering a SQL Error when executing SQL via PeopleCode that is calculating dates. This code was working prior to the PeopleTools upgrade from version 8.54.18 to 8.55.03 (non-production environment).


If I change the input parameters from Date variables to String variables then the SQL executes successfully, however, they should and were accepting date variables prior to the PeopleTools upgrade.

I’ve verified that the Date variables are in the expected DB2 date format (&date2 =2016-12-31 &date1 =2015-10-01).

These SQL statements work successfully when ran directly against the DB2 database via Command Editor.

Here are the SQL commands that all produce this error:
• SQLExec("SELECT ROUND(MONTHS_BETWEEN(:1, :2),2) FROM PS_INSTALLATION", &date2, &date1, &DiffMnth);
• SQLExec("SELECT ROUND(MONTHS_BETWEEN(:1 || '-00.00.00.00000', :2 || '-00.00.00.00000'),2) FROM PS_INSTALLATION", &date2, &date1, &DiffMnth);
• SQLExec("SELECT INTEGER(MONTHS_BETWEEN(:1 || '-00.00.00.00000', :2 || '-00.00.00.00000')) FROM PS_INSTALLATION", &date2, &date1, &DiffMnth);
• SQLExec("SELECT DATE(:1 || '-00.00.00.00000') + :2 MONTHS FROM PS_INSTALLATION", &date1, &DiffMnth, &PlusDiffMnth);
• SQLExec("SELECT DAYS (DATE(:1 || '-00.00.00.00000')) - DAYS (DATE(:2 || '-00.00.00.00000')) FROM PS_INSTALLATION", &date2, &PlusDiffMnth, &DiffDays);


 

ERROR
-----------------------

Return code 8601 Statement contains an unsupported character.


BUSINESS IMPACT
-----------------------------

Upgrading to PT 8.55 and not in production yet.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms