E-QR: Connected Query Does Not Display The Date Fields In Standard Format
Last updated on MARCH 30, 2016
Applies to:PeopleSoft Enterprise PT PeopleTools - Version 8.51 and later
Information in this document applies to any platform.
***Currency Checked January 2014***
***Checked For Relevance On 30 March 2016***
When attempting to run the Connected Query to XML output it does not keep the date format in standard/canonical date format. The standard/canonical date format expected should be in this format YYYY-MM-DD, but the Connected Query Tool shows the US date format (MM/DD/YYYY).
In addition, there are differences between PSQuery, XML Publisher tools versus Connected Query. When trying to preview the output data in the xml files the PSQuery, XML Publisher tools show the output data(for date data) in this format YYYY/MM/DD HH24:MI:SS and the Connected Query shows the output data in US date format(for date data).
The issue can be reproduced at will with the following steps:
1. Login to PIA
2. Navigate to Reporting Tools > Query Manager > Query
3. Create new 2 queries: SB_QRY1, SB_QRY2
4. Run the SB_QRY1 query to xml and see that the date is in Oracle Canonical Date Format (YYYY/MM/DD HH24:MI:SS)
5. Navigate to Connected Query > Connected Query Quick Start and create a new connected query: SB_XMLDATETEST based on SB_QRY1 and SB_QRY2 queries
6. Navigate to Connected Query > Connected Query Manager and see the new connected query created:
7. Navigate to Connected Query > Connected Query Viewer
8. Preview XML
Note that Date fields are all being output as MM/DD/YYYY (US Format).
9. Navigate to Reporting Tools > XML Publisher > Data Source
10. Create a new data source based on SB_XMLDATETEST connected query
11. Generate the Sample Data File
12. Select the generated sample XML.
Note that dates in the sample data are coming off as it is expected in YYYY/MM/DD HH24:MI:SS format.
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