CODEWITHSUNDEEP
javaoracle-databasejava-8oracle12c
Mr.X
Mr.X

Reputation: 117

Getting ORA-01858 error while inserting time stamp

My timestamp format in front end is like this

2020-7-23 14:36:43. 132000000

My timestamp in oracle db is like this

23-07-20 02:36:43.132000000 PM

I have changed the time format like below

private String OracleTsString(String createdDate)  {
        SimpleDateFormat inputFormat=null;
        SimpleDateFormat outputFormat = null;
        try {
        
          inputFormat = new SimpleDateFormat("yyyy-M-dd HH:mm:ss.SSSSSSSSS");
          Date date;
          date = inputFormat.parse(createdDate);
           outputFormat = new SimpleDateFormat("MM-dd-yy hh:mm:ss.SSSSSSSSS aa");
        
        }
   //Exception Handling

But when I try to insert the string into database , I am getting ORA-01858: a non-numeric character was found where a numeric was expected

Below is my query

jdbcTemplate.update(query, 
                editSftpBean.getSftpName(),
                editSftpBean.getIp_address(),
                editSftpBean.getPort_number(),
                editSftpBean.getUserName(),
                editSftpBean.getAuthentication(),
                oracleTs//My converted string
);

insert query::

 INSERT INTO tabl_two (SFTP_NAME, IP_ADDRESS, PORT_NUMBER, USER_NAME, AUTH_TYPE,CREATED_DATE ) VALUES (?, ?, ?, ?, ?)

How can I resolve this?

Upvotes: 1

Views: 192

Answers (1)

Andreas
Andreas

Reputation: 159086

Don't use a String to insert a date/time value into the database, use a Timestamp.

private static Timestamp parseTsString(String createdDate) {
    DateTimeFormatter fmt = new DateTimeFormatterBuilder()
            .appendPattern("uuuu-M-d H:mm:ss")
            .appendFraction(ChronoField.NANO_OF_SECOND, 0, 9, true)
            .toFormatter();
    return Timestamp.valueOf(LocalDateTime.parse(createdDate, fmt));
}

Upvotes: 3

Related Questions