parse String variable from database (type datetime) to LocalDateTime variable using Resultset

Question

I'm trying to convert a String value (initially a LocalDateTime variable) that was stored in a database (as datetime) and parse it into a LocalDateTime variable. I've tried it with a formatter:

String dTP;
dTP=(rs.getString("arrivedate"));
            DateTimeFormatter formatter = DateTimeFormatter.ISO_LOCAL_DATE_TIME;
            LocalDateTime dateTimeParked = LocalDateTime.parse(dTP,formatter);

And without a formatter:

String dTP;
dTP=(rs.getString("arrivedate"));
LocalDateTime dateTimeParked = LocalDateTime.parse(dTP);

But I get the same error each time:

Exception in thread "AWT-EventQueue-0" java.time.format.DateTimeParseException: Text '2016-07-09 01:30:00.0' could not be parsed at index 10

My thinking is that index 10 is the space between date and time.

Could anyone help me with this? I've been at it for hours :(

score 1 · Accepted Answer · answered Jul 25 '16 at 04:37

There is a error in the format of the that causes the issue. Please refer https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html.The ISO date time is of the format '2011-12-03T10:15:30' . The following will give you the idea

public static void main(String[] args) throws Exception {


    String isoDate = "2016-07-09T01:30:00.0";
    //  ISO Local Date and Time '2011-12-03T10:15:30'
    DateTimeFormatter formatter = DateTimeFormatter.ISO_LOCAL_DATE_TIME;
    LocalDateTime dateTimeParked = LocalDateTime.parse(isoDate, formatter);
    System.out.println(dateTimeParked);




    String date = "2016-07-09 01:30:00.0";
    DateTimeFormatter formatterNew = DateTimeFormatter.ofPattern("yyyy-LL-dd HH:mm:ss.S");
    LocalDateTime dateTimeParkedNew = LocalDateTime.parse(date, formatterNew);      
    System.out.println(dateTimeParkedNew);

}

This prints : 2016-07-09T01:30 2016-07-09T01:30

Thanks a lot guys! It works great now. @Ramachandran G A – Wachaga Mwaura Jul 25 '16 at 18:00 — Wachaga Mwaura, Jul 25 '16 at 18:00

Basil Bourque · Answer 2 · 2018-06-11T01:22:56.883

The other answers are correct, your string is in SQL format which differs from the canonical version of ISO 8601 format by using a space character in the middle rather than a T. So either replace the space with a T or define a formatting pattern for parsing.

Use smart objects, not dumb strings

But the bigger problem is that you are retrieving the date-time value from your database as a string. You should be retrieving date-time types of data as date-times types in Java.

For drivers compliant with JDBC 4.2 and later, you should be able to use setObject and getObject with java.time objects.

For SQL type of TIMESTAMP WITHOUT TIME ZONE use LocalDateTime. For TIMESTAMP WITH TIME ZONE, use Instant or perhaps ZonedDateTime depending on the database.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class );

Store in database.

myPreparedStatement.setObject( … , ldt ) ;

Anupam Jain · Answer 3 · 2016-07-25T04:31:13.793

0

try this formatter:

DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.S");

I'm not sure about the millisecond part though (In case it is more than 1 character long).

edited Jul 25 '16 at 04:31

answered Jul 25 '16 at 04:25

Anupam Jain

101
10

This thread should help you further: http://stackoverflow.com/questions/1459656/how-to-get-the-current-time-in-yyyy-mm-dd-hhmisec-millisecond-format-in-java – Anupam Jain Jul 25 '16 at 04:30

parse String variable from database (type datetime) to LocalDateTime variable using Resultset

3 Answers3

Use smart objects, not dumb strings

Linked