Convert a locale date into mysql datetime format

Question

I have the following String I get from result. How can I intelligently convert it into mysql date time format in java?

Montag, 09. April 2018, 11:00 Uhr

Thanks in advance

Answer 1

tl;dr

Send an object to your database, rather than a string.

myPreparedStatement.setObject(                      // JDBC 4.2 and later allows direct exchange of java.time objects with a database. 
    … ,                      
    DateTimeFormatter.ofPattern(                    // Define a formatter based on a specific pattern.
        "EEEE, dd. MMMM yyyy, HH:mm 'Uhr'" ,        // Pattern to match our input text.
        Locale.GERMANY                              // Locale specifies human language used to parse the name of day, name of month, etc.
    ).parse( "Montag, 09. April 2018, 11:00 Uhr" )  // Generate a `ZonedDateTime` object. 
)

Smart objects, not dumb strings

First, read the correct Answer by gil.fernandes.

Your phrase “mysql date time format” suggests working with strings. You should not be exchanging date-time values with your database as text. We have classes for that.

Parse your input string into a LocalDateTime object as shown in that answer.

DateTimeFormatter f = 
    DateTimeFormatter.ofPattern(
        "EEEE, dd. MMMM yyyy, HH:mm 'Uhr'" , 
        Locale.GERMANY
    )
;
LocalDateTime ldt = 
    LocalDateTime.parse( 
        "Montag, 09. April 2018, 11:00 Uhr" , 
        f 
    ) 
;

With JDBC 4.2 or later, send that object to your database. Note that LocalDateTime should only be sent to a column of type like the SQL standard TIMESTAMP WITHOUT TIME ZONE.

myPreparedStatement.setObject( … , ldt ) ;

Retrieval:

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

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android, the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Answer 2

You can effectively do it, but I do not know if this is a good idea.

Anyway this is what would technically work:

String s = "Montag, 09. April 2018, 11:00 Uhr";
DateTimeFormatter formatter = DateTimeFormatter
            .ofPattern("EEEE, dd. MMMM yyyy, HH:mm 'Uhr'", Locale.GERMANY);
// Parse with the German format
LocalDateTime dateTime = LocalDateTime.parse(s, formatter); // Using Java 8 libraries
// Format with the MySQL format (Well, actually ISO)
final DateTimeFormatter formatterMysql = DateTimeFormatter.ISO_DATE_TIME;
System.out.println(formatterMysql.format(dateTime).replace("T", " "));

This will print out:

2018-04-09 11:00:00

Which should be understood by MySQL.