Hello Everyone,
I am facing issue with Datetime field while inserting data from local entity to external entity created on oracle database table.
Local entity shows datetime, post insert into external entity i only see date but time is not present.
In local entity created in Outsystems - datatype is text
In external entity on oracle database - datatype is date
I tried all the possibilities of TextToDateTime, BuildDateTime, FormatDateTime etc.. but no luck.
Note: Data to local entity is uploaded via excel by storing it in Outsystems in binaryfile format.
Data to external entity is loaded via sql & crud operation.
Any suggestions on how to deal with this issue?
Please try using the NewDateTime(,,,,,) function to see if it works for you. You need to compose a new DateTime from your text value in the OutSystems entity, since your data type is text. Could you please provide a few sample values of your records?
Hi Siya,
Please find below details:
NewDateTime(
TextToInteger(Substr(DATETIME, 6, 4)),
TextToInteger(Substr(DATETIME, 3, 2)),
TextToInteger(Substr(DATETIME, 0, 2)),
TextToInteger(Substr(DATETIME, 11, 2)),
TextToInteger(Substr(DATETIME, 14, 2)),
If(Substr(DATETIME, 17, 2) = "", 0, TextToInteger(Substr(DATETIME, 17, 2)))
)
Tried above one as well by creating a server action and making it as a function and used it while inserting through advanced sql
Text format: 8-4-2024 13:00:00
Also tried by hardcoding value
TO_DATE('8/4/2024 13:00:00', 'DD/MM/YYYY HH24:MI:SS') in advanced sql.
In all the cases i see the data in database as 8/4/2024. When queried with to_char by applying format mask in oracle database i see value as 8/4/2024 12:05:00.
Nothing is working!
Thanks for the update. Can you check the data type for this particular field in Integration Studio? Is it date or date time ? eg :
It is Date Time.
Can i suspect any setting to be changed in oracle database or formatting the excel before processing?
I think knowing what query is being executed on Oracle would help isolate the issue. Could you execute `SELECT sql_text FROM v$sql` on your Oracle server to see if the query executed from OutSystems is present there?
Thank you!
I queried v$sql and the insert statement is as expected with correct date time TO_DATE('8/4/2024 13:00:00', 'DD/MM/YYYY HH24:MI:SS') .
I did a manual insert into the table via toad and it is not inserting timestamp.
Checked NLS parameters and date format is set to DD-MON-RR.
Hope it is issue at oracle settings. Any suggestions to fix it pls..
Thanks for the update. You earlier mentioned that "when queried with TO_CHAR by applying a format mask in Oracle database, I see the value as 8/4/2024 12:05:00," indicating a 55-minute difference. Can you try inserting a different time and see if this difference remains the same? Also, can you check the time zone of the Oracle server by running:
SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;
Is this different from the platform server?
Pls find below details.
Outsystems server is on one VM and Oracle Database is on different VM
DBTIMEZONE, SESSIONTIMEZONE
+02:00   +02:00
when applied to_char today on manual insert and inserts via outsystems i see time as 00:00:00 irrespective of what ever value is there during inserts.
Would it be possible to alter the NLS_DATE_FORMAT format as "ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH24:MI:SS';" . Do this with caution as this may impact other applications.
btw please create a temporary table with a date time field and try inserting date time and see if that get inserted properly. If so compare that query with the one sent from. the OutSystems server.
Issue resolved. There was a database trigger which is doing a trim on all the fields inserted into oracle table. That was causing the issue. Removed trim option for date fields and it is working now.
Thanks for the update. Glad to hear that issue is resolved.
Thank you for your help Siya