How to transform ISO DATE to Timestamp in Java or SQL?

Question

I am using Mongo and Oracle 12c at the same time.

For MongoDB I am using ISO DATE (but like String) to persist date. Like this:

{
            "_id" : null,
            "fields" : {
                "Status" : "UnderInvestigation",
                "Assignee" : "",
                "CreationDate" : "2016-12-14T00:00:00Z", //ISO DATE
                "CaseId" : "8165021",
                "RunningMode" : "STS",
                "CloserDueDate" : ""
            },
            "domain" : {},
            "arrays" : {}
        }

I want use Timestamp format type in Oracle for a column with the same name, so the idea is transform that date to timestamp.

I don't know how to do that. I have the follow code but it doesn't work.

final String query = "INSERT INTO " + TABLE_APPLICATION + " (CreationDate) VALUES (TO_DATE(?,'YYYY-MM-DD HH24:MI:SS'))" ;
PreparedStatement ps = getPreparedStatement();
((OraclePreparedStatement) ps).setString(1, getValueFromJson(JSON, 'fields.CreationDate'));

But I got:

java.sql.SQLDataException: ORA-01861: el literal no coincide con la cadena de formato

What's the problem?

Answer 1

Oracle solution:

SELECT TO_TIMESTAMP_TZ(
         '2016-12-14T00:00:00Z',
         'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM'
       )
FROM   DUAL;

So your code should be:

final String query = "INSERT INTO " + TABLE_APPLICATION + " ("
                        + "CreationDate"
                        + ") VALUES ("
                        + "TO_TIMESTAMP_TZ( ?, 'YYYY-MM-DD\"T\"HH24:MI:SSTZH:TZM')"
                        + ")";