2

I wanted to parse a date using LocalDateTime.parse() method with the date format yyyy-MM-dd HH:mm:ss, but what I actually get is a date of format yyyy-MM-ddTHH:mm:ss. I don't need that 'T'. Please see below the code

LocalDateTime.parse("2016-10-31 23:59:59", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

The result I am getting is 2016-10-31T23:59:59. See that 'T'. The 'T' is causing issues so that I am unable to persist it to my database. I try to persist the value in a column of type datetime; I am getting the error - org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar.

See the values which are working

VALUES('US','101','test','firstname','middleName','preferedN‌​ame','lastName',
       '198‌​9-01-01','M',1,'1122‌​1123','test@test.com‌​','address1','addres‌​s2','Bloomingdale','‌​IL','US','689850',
       1,‌​1,'11111','2016-12-3‌​1 23:59:59')

(no T in last value)

which is not working:

VALUES('US','101','test','firstname','middleName','preferedN‌​ame','lastName',
       '198‌​9-01-01','M',1,'1122‌​1123','test@test.com‌​','address1','addres‌​s2','Bloomingdale','‌​IL','US','689850',
       1,‌​1,'11111','2016-12-3‌​1T23:59:59')

(with the T in the last value).

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
ajith george
  • 538
  • 1
  • 5
  • 14
  • What is the question? – Robin Topper Apr 11 '17 at 07:15
  • 1
    What you see is the internal representation of the Date. Because a `Date` has no format – Jens Apr 11 '17 at 07:16
  • 2016-10-31T23:59:59 - The 'T' is causing issues so that I am unable to persist it to my database. – ajith george Apr 11 '17 at 07:17
  • 1
    What is the type of the column, How do you try to store the date, what is the error message? – Jens Apr 11 '17 at 07:18
  • from the javadoc of `LocalDateTime`, as `parse` returns one: `A date-time without a time-zone in the ISO-8601 calendar system, such as 2007-12-03T10:15:30. `, you´re not really formatting, you´re just seeing what `LocalDateTime#toString` does print, where the code simply does `return date.toString() + 'T' + time.toString()`. There´s your `T`, it´s not bound to any Format and is just a literal beeing printed there. – SomeJavaGuy Apr 11 '17 at 07:19
  • 3
    Date/Time objects **DO NOT** have a concept of formatting, they are containers for the number of milliseconds from a given point in time. This is why there a date/time formatters - I'd recommend having a look at [Parsing and Formatting](https://docs.oracle.com/javase/tutorial/datetime/iso/format.html) trail – MadProgrammer Apr 11 '17 at 07:19
  • 1
    type of the colume is datetime , I am getting the error - org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar – ajith george Apr 11 '17 at 07:20
  • 1
    So Show the relevant code. It has nothing todo with the date – Jens Apr 11 '17 at 07:23
  • See the values which are working VALUES('US','101','test','firstname','middleName','preferedName','lastName','1989-01-01','M',1,'11221123','test@test.com','address1','address2','Bloomingdale','IL','US','689850',1,1,'11111',**'2016-12-31 23:59:59'**) – ajith george Apr 11 '17 at 07:26
  • which is not working VALUES('US','101','test','firstname','middleName','preferedName','lastName','1989-01-01','M',1,'11221123','test@test.com','address1','address2','Bloomingdale','IL','US','689850',1,1,'11111',**'2016-12-31T23:59:59'**) – ajith george Apr 11 '17 at 07:26
  • Use a `PreparedStatement` and a `java.sql.Timestamp` and let the database sort it out – MadProgrammer Apr 11 '17 at 07:28
  • 1
    @ajithgeorge as mentioned before, you wont get rid of the `T` with your current implementation (well you didn´t provide it), as you probably just print out the `LocalDateTime#toString` inside your SQL statment. The `T` here is a fix literal in `LocalDateTime#toString` which you wont get rid of. Instead you might want to check [this question](http://stackoverflow.com/questions/22463062/how-to-parse-format-dates-with-localdatetime-java-8) on how to properly format or use MadProgrammer's hint. – SomeJavaGuy Apr 11 '17 at 07:28
  • 1
    check this: http://stackoverflow.com/questions/33337487/localdate-how-to-remove-character-t-in-localdate – blu3 Apr 11 '17 at 07:30
  • What’s the point in parsing your date if what you care about is the string that you had from the beginning (with no `T` in it)? – Ole V.V. Apr 11 '17 at 07:45
  • I think you should edit your question and add the information about the values that work and those that don’t work when inserting into your database. – Ole V.V. Apr 11 '17 at 07:48
  • 1
    @MadProgrammer, I’ve read that new JDBC drivers accept `java.time` class instances so you need not use the oldfashioned `java.sql.Timestamp`. If you aren’t lucky to have a new enough driver, converting from `Instant` to `Timestamp` is as easy as `Timestamp.fromInstant()`. – Ole V.V. Apr 11 '17 at 07:50
  • @OleV.V. Been a LONG time since I had to interact with jdbc and all my interactions with the new date/time API is what I do in my personal time - having said, parsing the value to String is the wrong approach – MadProgrammer Apr 11 '17 at 08:16

1 Answers1

7

LocalDateTime is not stored as string, but as object.

You are getting a "T" because the .toString() method return the ISO format by default, as stated in: https://docs.oracle.com/javase/8/docs/api/java/time/LocalDateTime.html

toString

public String toString()

Outputs this date-time as a String, such as 2007-12-03T10:15:30.

The output will be one of the following ISO-8601 formats:

uuuu-MM-dd'T'HH:mm
uuuu-MM-dd'T'HH:mm:ss
uuuu-MM-dd'T'HH:mm:ss.SSS
uuuu-MM-dd'T'HH:mm:ss.SSSSSS
uuuu-MM-dd'T'HH:mm:ss.SSSSSSSSS

The Format used will be the shortest that outputs the full value of the time where the omitted parts are implied to be zero.

To output as you need use format function.

LocalDateTime d = LocalDateTime.parse("2016-10-31 23:59:59", DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

System.out.println("toString: " + d.toString());
//toString: 2016-10-31T23:59:59                                                                                                                                                                                                                           
System.out.println("format:   " + d.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
//format:   2016-10-31 23:59:59
Luiz Vaz
  • 1,669
  • 1
  • 19
  • 32