Unconvertional dates uploaded to database

, ,
1

Dear Community,

we are currently having an issue regarding uploading timestamps to an Oracle database. The column type in the database is TIMESTAMP, and we would like to upload it as a string.

No problems so far, we have synced the receiving and uploading types as strings, and we use the getCurrentDateString service, with “yyyy-MM-dd-HH.mm.ss.SSS” pattern.

The upload proceeds and works without errors, however, when we check the database, we notice, that the dates are not accurate at all.

For example:
2016-03-06 01:51:23
2012-10-06 01:10:49

The time and day seem to be working, but the month and year parameters display extremely wrong values at certain times, in a random manner.

Any help and ideas are greatly appreciated.

Best regards,
Ákos

2

We have used SetAsString in the InputFiledType and in expression used to_date(?, ‘oracle-format of the date’); this was reliable for us than the default jdbc conversions.
hope this helps.
-Deepan

3

The format of the date/time string must match the default format of the DB. Based upon your description “yyyy-MM-dd-HH.mm.ss.SSS” is not likely to be what the DB uses.

You have choices here:

  • Since you’re calling getCurrentDateString to get “now”, one option is to not set that column via IS at all. Let the DB default it–assuming the column is defined with ‘default sysdate’ or default systimestamp’

  • Use the string format of the DB when calling getCurrentDateString. Search the web for details in how to determine what the default format is.

  • As Deepak mentions, you can convert the string to a DB date object using to_date where the format string matches the format of the date value string. But based on what you’ve posted, this probably isn’t necessary. Just get the date string in the right format and the driver and DB will handle the conversion for you.

4

First and foremost, thank you very much for your replies.

@reamon: “Unfortunately”, our date format is fine. We are using an Oracle database, tried using the corporate standard timestamp formats, and the JDBC adapter immediately replied us the correct timestamp format we need to use.

@Deepan_C, reamon: Thanks for the ideas, I will compose a service that works with that method.

Best regards,
Ákos

5

I’ve successfully inserted current date into Oracle DB using the following:

Service: pub.date.getCurrentDate

Mapped to adapter input
Input field: java.sql.Timestamp
JDBC type: TIMESTAMP

I think I’ve also inserted custom dates but I can’t find where atm, but I think I somehow used that java.sql.Timestamp format then too.

Not sure if this helps but more information on this subject at least.

/Hyui

6

Thanks Hyui, this seems to have solved our problem!

~akos