how to convert String to java.sql.Date?

Question

I need to convert a string in the format i.e. "jan 25,2021" to a java.sql.date in the format "2021-01-25". I tried to use this code:

String date = "jan 25,2021";
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
java.sql.Date booking_date=null;
try{
java.util.Date util_date = format.parse(date);
booking_date = new Date(util_date.getTime());
}
catch(ParseException e){
            System.out.println(e);
        }

But always throws a ParseException. Where is the mistake?

Answer 1

I need to convert a string in the format i.e. "jan 25,2021" to a java.sql.date in the format "2021-01-25". I tried to use this code:

That's like asking: "How do I convert a painting into an audio file" - these 2 concepts are unrelated. They sound related (jan 25, 2021 is a date, and, well, java.sql.Date, it's right there in the name, isn't it), but they are not: java.sql.Date is a misnomer - it does not represent a date at all; it represents an instant in time. Which is not a date: If I clap my hands and ask everybody around the world what date it is when I did that, you'll get at least 2 different answers, thus proving that these 2 are not the same.

The right solution is to use the newer API which doesn't suffer from such errors:

DateTimeFormatter FORMAT = DateTimeFormatter.ofPattern("MMM dd,uuuu", Locale.ENGLISH);
LocalDate ld = LocalDate.parse("jan 25,2021", FORMAT);

I would expect .setObject(x, someLocalDate) to work as a date value, but if it doesn't (and it may not - mysql is notoriously bad, and its JDBC driver is dubious as well), at least convert from the proper representation (LocalDate) to the broken one (java.sql.Date) as late as possible - don't let that error infect your java code. Same goes in reverse: When retrieving dates, use .getObject(idx, LocalDate.class), and if that does not work, convert from the broken java.sql.Date to the correct java.time.LocalDate immediately, prevent that error from infecting your java code as much as possible.

Answer 2

I need to convert a string in the format i.e. "jan 25,2021" to a java.sql.date in the format "2021-01-25".

A date-time object is supposed to store the information about date, time, timezone etc., not about the formatting. You can format a date-time object into a String with the pattern of your choice using date-time formatting API.

• The date-time formatting API for the modern date-time types is in the package, java.time.format e.g. java.time.format.DateTimeFormatter, java.time.format.DateTimeFormatterBuilder etc.
• The date-time formatting API for the legacy date-time types is in the package, java.text e.g. java.text.SimpleDateFormat, java.text.DateFormat etc.

Switch to the modern date-time API

The date-time API of java.util and their formatting API, SimpleDateFormat are outdated and error-prone. The java.sql.Date extends java.util.Date and thus inherits the same problems. It is recommended to stop using them completely and switch to the modern date-time API.

• For any reason, if you have to stick to Java 6 or Java 7, you can use ThreeTen-Backport which backports most of the java.time functionality to Java 6 & 7.
• If you are working for an Android project and your Android API level is still not compliant with Java-8, check Java 8+ APIs available through desugaring and How to use ThreeTenABP in Android Project.

Using the modern date-time API:

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.util.Locale;

public class Main {
    public static void main(String[] args) {
        String strDate = "jan 25,2021";
        DateTimeFormatter dtf = new DateTimeFormatterBuilder()
                                    .parseCaseInsensitive() //For case-insensitive parsing
                                    .appendPattern("MMM d,uuuu")
                                    .toFormatter(Locale.ENGLISH);

        LocalDate date = LocalDate.parse(strDate, dtf);
        System.out.println(date);
    }
}

Output:

2021-01-25

Once parsed into LocalDate, you can use it with JDBC 4.2 onwards as follows (assuming that the column type is DATE):

LocalDate localDate = LocalDate.now();
PreparedStatement st = conn.prepareStatement("INSERT INTO mytable (columnfoo) VALUES (?)");
st.setObject(1, localDate);
st.executeUpdate();
st.close();

Learn about the modern date-time API from Trail: Date Time.

Answer 3

Your supplied format is in the not in the form of year-month-day like the text your formatter expects (i.e. "2021-01-25), so use this instead:

SimpleDateFormat format = new SimpleDateFormat("MMM dd,yyyy");

And you'll be able to correctly parse the date in your format. Otherwise change the argument itself to match the expected pattern (i.e. pass "2021-01-25" as the string).

Edit: This is strictly referring to the code at hand and the format mismatch. See the other, fuller answers for using the newer (and better) APIs that address issues with the old time-related libraries.