Date format difference between PFI and LPA?

While migrating our flows from PFI to LPA and have found that the date format returned from SQL query node is different. A SQL query (Select date_hired from test.employee) returns 1997-07-14 from PFI and 1997-07-14 00:00:00.0 from LPA. This affects date calculations, date logic, and date display in our process flows. We are told that the "dates in the PFI tables" have all been changed from date to date-time. This answer is confusing, since we are not querying a PFI table (LOGAN or LMKGEN), but an application table, HR's employee table. I find it hard to believe that all of the application date formats have been changed. Can anyone suggest why this is happening and if there is anything we can do to get the same result as on PFI - instead of having to revise our flow date handling?

Find more posts tagged with

Process Automation (IPA) - General Discussions

Comments

john-miller

Can you give an example of how you're calculating the dates? Such as: Type of node doing the calculation, variable type and command (SQL Node using Date calculations vs an Assign node using JavaScript)

unknown

No examples yet - at this point I've stopped migrating flows until I understand the root cause of the date display format difference. I'm only anticipating that if the display format is different, that it will likely also affect calculations, comparisons, and other date handling in the flows - so no examples of those yet. I wanted to be sure this wasn't just a configuration setting set incorrectly before I start mitigating for it in our flows.

unknown

Hi BarbR,

You might want to confirm (or have someone confirm) that the SQL driver that was installed on the Landmark server for IPA is the same one that was installed on LSF for ProcessFlow. We use IPA with AIX/DB2 on both Landmark and LSF, and the dates come across as dates from both systems. It seems to me that a different driver could behave differently for something like this.

Unfortunately, I can't tell you where to look to find these - but someone in your organization (or whoever manages your systems) should be able to tell.

Good Luck.

Kelly

0911060553270289.pdf

russel-cornelson

Good afternoon Barb,

The problem you are encountering is actually not a problem with Infor Process Automation. When you build a query using the SQL Node process flow will simply return the data that your SQL Statement Generates.

This worked for you in PFI, because you were using LSF9.

In LSF9; the EMPLOYEE.DATE_HIRED field was of datatype = "DATE" (YYYY-MM-DD)

In LSF9; the EMPLOYEE.DATE_HIRED field is of datatype = "DATETIME" (YYYY-MM-DD HH:MM:SS.f)

So if you had a record in LSF9, that was "1997-07-14" and you migrated to LSF10. The value now stored in the database is "1997-07-14 00:00:00.0" ... process flow's SQL node simply returns the data that's in the system.

To resolve this issue; without modifying the EMPLOYEE Database table and altering datatype and underlying data...

... modify the SQL Query you are sending using a CONVERT statement. (see example)

or

... modify the returned result of that field using javascript to convert the datetime to a date.

For example:

select convert(date,DATE_HIRED,113) FROM [L10CUR].[l10cur].[EMPLOYEE]

This will convert the DATETIME field and output ... YYYY-MM-DD format (SQL Server 2008) exact syntax of converting will likely alter depending on your Database.

unknown

Ahhh - good catch Russel. I didn't catch that there was an upgrade involved, though I should have. That's a bummer from a conversion perspective. What brainiac decided that a Hire Date field would need a timestamp? DOH! What a pain!

unknown

I opened an Incident and so far we are being told that all dates are now date-time. I find that very hard to believe, I would think I'd have heard other customer posts about this if it were true. I don't think the last shoe has dropped on this yet.

russel-cornelson

Good afternoon Barb,

I checked about 15 tables, did not have time to look at them all. But every LSF10 table I looked at had DATETIME datatypes for the date fields.

To confirm my earlier explanation, I created a test flow in Process Automation to query an LSF9 database and asked for the DATE_HIRED records. It returns the data as stored in the database "YYYY-MM-DD" format. I then changed my configuration to connect to an LSF10 database and ran the same flow and it returns "YYYY-MM-DD HH:MM:SS.ffff" format.

From a process flow standpoint this is working normally, in order to pursue this further I think the LSF10 Database team or LSF Application team would need to be involved.

russel-cornelson

Good afternoon Barb, I am going to do this in point form:

1. I am so sorry, my original testing that I performed had a mistake in it. While I created the correct configurations to call the LSF9 and LSF10 databases in Oracle and SQL ... I did not have the correct configurations selected in testing. This lead me to believe I was correct in my original assessment. Alas, I was not, and I am sorry if I lead anyone in the wrong direction.

After that realization, I re-ran all my tests and found you are indeed correct. For an ORACLE database LSF9 or LSF10 ... the new IPA Designer will indeed return (YYYY-MM-DD HH:MM:SS.f) even when it queries a straight up DATE field.

2. Upon realizing this, I started digging in further and I found

... any ODBC connection, like one from TOAD will return a DATE field in the YYYY-MM-DD format

... any JDBC connection, like the one in DbVisualizer or IPA will return YYYY-MM-DD HH:MM:SS.f

3. The behavior of how JDBC returns DATE data has changed.

...JDBC drivers from oracle before 9i returned DATE=YYYY-MM-DD HH:MM:SS.f

...JDBC drivers from oracle 9i and 10 returned DATE=YYYY-MM-DD

...JDBC drivers from oracle 11 changed back and DATE = YYYY-MM-DD HH:MM:SS.f

4. Infor Process Automation is delivered with an Oracle JDBC driver that falls into the last category and thus returns date fields in the format of YYYY-MM-DD HH:MM:SS.f

5. In my research I found there is a property we can set to revert the behavior back to it's Oracle 9i way.

To fix this in your IPA Designer:

- open C:IPDesigner<installname>IPDesigner.ini

- add the following line at the bottom of this file: -Doracle.jdbc.mapDateToTimestamp=false

- completely stop and start the designer

To fix this in IPA:

- open the Landmark Grid Management UI

- click the gears in the top right corner

- click "Applications" link

- click "Landmark Application Link"

- click "Edit Properties"

- press CTRL-F to open the search box in bottom left corner; search for "Generic JVM Commands"

- click the words "Generic JVM Commands", not the <undefined> or < [# Entries]

- click the "All" radio button

- scroll down till you find your LPA Binding (LPA or LmrkDeferred)

- click the <inherited> link on the left column of the light blue LPA line

- make sure the radio button has "Append" selected

- in the empty line add "-Doracle.jdbc.mapDateToTimestamp=false"

- click save in the popup window

- click save in the top left corner

- click the house in the top right corner to got home page

- stop the LPA node and let it restart.

Summary

If you are using a Oracle database that is Oracle 11 or higher the new oracle JDBC drivers return DATE fields with TIMESTAMP information on them by default. You can revert this behavior by adding a parameter to the designer or LPA grid node.

TEST BEFORE CHANGES

SQL SQLQuery4730: Using JDBC connection String Driver: oracle.jdbc.driver.OracleDriver, URL: jdbc:oracle:thin:@SERVER:PORT:SID, User:USERNAME

SQL Query SQLQuery4730: Query string select DATE_HIRED from EMPLOYEE WHERE EMPLOYEE = 1065

SQLQuery4730_errorCode = 0

SQLQuery4730_informationCode = 0

SQLQuery4730_returnMessage = SQL query SQLQuery4730: Execution complete.

SQLQuery4730_outputData =

Activity name:SQLQuery4730 id:2 completed @ 09/01/2016 05:27:50.634 PM

SQLQuery4730_RETURN_MSG = Success

SQLQuery4730_RETURN_CODE = 0

SQLQuery4730_errorCode = 0

SQLQuery4730_informationCode = 0

SQLQuery4730_returnMessage = SQL query SQLQuery4730: Execution complete.

SQLQuery4730_outputData =

SQLQuery4730_RECORD_COUNT = 2

SQL Query SQLQuery4730: Executing loop 1 of 2

SQLQuery4730_1 = 2002-10-26 00:00:00.0

SQLQuery4730_DATE_HIRED = 2002-10-26 00:00:00.0

TEST AFTER CHANGES

SQL SQLQuery4730: Using JDBC connection String Driver: oracle.jdbc.driver.OracleDriver, URL: jdbc:oracle:thin:@SERVER:PORT:SID, User:USERNAME

SQL Query SQLQuery4730: Query string select DATE_HIRED from EMPLOYEE WHERE EMPLOYEE = 1065

SQLQuery4730_errorCode = 0

SQLQuery4730_informationCode = 0

SQLQuery4730_returnMessage = SQL query SQLQuery4730: Execution complete.

SQLQuery4730_outputData =

Activity name:SQLQuery4730 id:2 completed @ 09/01/2016 05:56:51.587 PM

SQLQuery4730_RETURN_MSG = Success

SQLQuery4730_RETURN_CODE = 0

SQLQuery4730_errorCode = 0

SQLQuery4730_informationCode = 0

SQLQuery4730_returnMessage = SQL query SQLQuery4730: Execution complete.

SQLQuery4730_outputData =

SQLQuery4730_RECORD_COUNT = 2

SQL Query SQLQuery4730: Executing loop 1 of 2

SQLQuery4730_1 = 2002-10-26

SQLQuery4730_DATE_HIRED = 2002-10-26

russel-cornelson

NOTE: While the reason this behavior changed is actually due to third party code, in this case oracles jdbc driver, I think we could actually react to this internally to prevent it from changing the way flows run.

I think we could simply deliver the above settings in our LPA binding and in our designer, I am going to bring it up with our development team and see if they would consider implementing the changes in our next release automatically.

unknown

Russel Cornelson - thank you So much! Your instructions were detailed and clear. We performed before and after tests from a run from the designer and a Rich Client trigger. The date format returned from SQL nodes is now back to the expected format. We certainly appreciate your involvement with this thread!

unknown

Russel- I am having this same issue and we are not oracle. We are also still LSF9. Is there a property that I can add to the grid to fix this? Thanks!

Activity started: SQLQuery4050 (Run Id: 5)

SQL Query SQLQuery4050: Query string SELECT EMPLOYEE.DATE_HIRED

FROM EMPLOYEE

WHERE EMPLOYEE.EMPLOYEE = 1531

SQL SQLQuery4050: Using JDBC connection String Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver, URL: jdbc:sqlserver://chslwtsql1.intra.chs-mi.com:1433;DatabaseName=TEST9, User: test

SQL Query SQLQuery4050: Query string SELECT EMPLOYEE.DATE_HIRED

FROM EMPLOYEE

WHERE EMPLOYEE.EMPLOYEE = 1531

SQLQuery4050_errorCode = 0

SQLQuery4050_informationCode = 0

SQLQuery4050_returnMessage = SQL query SQLQuery4050: Execution complete.

SQLQuery4050_outputData =

Activity completed: SQLQuery4050

SQLQuery4050_RETURN_MSG = Success

SQLQuery4050_RETURN_CODE = 0

SQLQuery4050_errorCode = 0

SQLQuery4050_informationCode = 0

SQLQuery4050_returnMessage = SQL query SQLQuery4050: Execution complete.

SQLQuery4050_outputData =

SQLQuery4050_RECORD_COUNT = 1

SQL Query SQLQuery4050: Executing loop 1 of 1

Activity started: SQLQuery4050 (Run Id: 5)

SQLQuery4050_1 = 1993-06-29 00:00:00.0

SQLQuery4050_DATE_HIRED = 1993-06-29 00:00:00.0

Copyright © 2025 Infor. All rights reserved.