E-QR: Query To Excel - EFFDT (DATE) From Unix Does Not Match EFFDT From Windows (Doc ID 2091597.1)

Last updated on JUNE 20, 2017

Applies to:

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

Symptoms

When attempting to run queries to Excel with more than 65k rows for very old date values incorrect date values are displayed in the output Excel (xlsx) file. Noticed, if the date value is '01-JAN-1900' the Excel output file shows "########################" pound signs, then moving the mouse cursor inside the cell it shows the "-1" value and shows the message "Date and Times that are negatives or too large displays as ################# ". If the date value is '01-JAN-1901' and rerun the Query to Excel the issue still may be reproduced, the Excel shows incorrect date value, it is displayed as '31-Dec-1900'.

This behavior occurs only on Windows. The Excel output file shows the correct date when run from Unix but sometimes the previous date when run from Windows. This is because Windows is calculating an epoch that is one hour too early, so 11 PM the previous day, for some dates.

Steps

--------
The issue can be reproduced at will with the following steps:
1. Login to PIA
2. Navigate to Reporting Tools > Query > Query Manager
3. Create a new PS Query using Date fields with blank values
4. Go to Run tab
5. Run the Query to HTML
6. Download the Query to Excel
7. There are very old date values (e.g. '01-JAN-1900', '01-JAN-1901) which DOES NOT displays correctly in the output Excel file, those are either displayed as pound signs or are different than the values that were entered in the database table.

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