How to Format Dates for SQL in Java

Say, you have a date that you need to store in a database record, and the date is entered as a string.

If you are like most beginners in Java, your first attempt will be to parse the string for a Date object by using the static method java.sql.Date.valueOf(String s). However, this will most likely not work as the input date is not in the format that is expected by the this method (i.e. yyyy-MM-dd).

The solution is to use a java.text.SimpleDateFormat object configured with the correct input pattern (e.g. dd/MM/yyyy) to parse the string for a java.util.Date object. The resulting date object can then be represented as a string in the yyyy-MM-dd format, which can be parsed by java.sql.Date.valueOf(String s).

This example demonstrates this.

This code creates a java.sql.Date object from a date value entered as a string by first formatting it to the ISO date standard format.

UPDATE: Of course, there is no need to strictly pass a java.sql.Date object to the java.sql.PreparedStatement.setDate() method as it happily accepts a java.util.Date.

17 Replies to “How to Format Dates for SQL in Java”

  1. I would recommend using the setLenient(false) method on the SimpleDateFormat object:

    This disables the use of heuristics when parsing the date, to avoid unpredictable results when the input does not exactly match the format.

    I would also recommend an extra precaution: the use of the java.text.ParsePosition.

    ParsePosition keeps track of the current position during parsing. After parsing, you can check the parsePosition.getIndex() to be the same as the parseFormat.length(). This ensures a correct parsing of the specified date using the specified date format.

    As to to actual problem you’re faced with, I don’t know a simpler way to achieve this.

  2. I cant believe how fd up java dates are…
    NONE of these work. ALL are documented
    // java.sql.Date dow = new java.sql.Date(System.currentTimeMillis());
    // java.sql.Date dow = new java.sql.Date(103,10,28);
    // java.sql.Date dow = new java.sql.Date.valueOf(“2003-01-01″);
    // java.sql.Date.valueOf(“2003-01-01″);

    How can rational humans have made a simple task so fd up???

  3. It May be that my use of JDBC-ODBC instead of pure JDBC means that lots of date stuff fails.
    Eddy Young’s code (above) is the only way I have managed to get a date into a sql JDBC-ODBC database. Apologies for my intemperate invective, but it took me best part of a day to find EY’s code. 29Oct03

  4. Hi,

    I have a java.util.Date, while setting this date in resultset.setDate() gives an error as it takes in java.sql.Date.
    How can i convert a java.util.Date to java.sql.Date

  5. A posting above claimed that four lines of code are documented but fail. They all worked for me, running on Mac OS X 10.2.8 with Java 1.4.1.

    My working code and comments follow. Paste this code into the “main()” method of any class.

    // Basil added this line, as Java does not let us
    // repetively declare the same var.
    java.sql.Date dow = null;

    dow = new java.sql.Date(System.currentTimeMillis());
    System.out.println( “dow 1: “+ dow ); // Should get today’s date.

    // This constructor of ints is deprecated but it works.
    // Note that Java’s Date class is zero-based in month count.
    // For the year, 103 is added to 1900 to get 2003.
    dow = new java.sql.Date(103,10,28);
    System.out.println( “dow 2: “+ dow ); // I get: 2003-11-28

    // Notice the “valueOf” method is static.
    // The fellow above incorrectly included a “new”.
    dow = java.sql.Date.valueOf(“2003-01-02″);
    System.out.println( “dow 3: “+ dow );

    // This next line makes no sense.
    // You are creating a java.sql.Date object, but the object
    // evaporates in the ether since you do not save a reference
    // to a variable. This code executes but does nothing useful.
    java.sql.Date.valueOf(“2003-01-03″);

  6. one easy way:
    convert sql Date to long type, and create util date from it:

    sDate is java.sql.date

    java.util.Date uDate = new java.util.Date(sDate.getTime()));

  7. halo, I would like to ask that after I use the valueOf, Can I use the date I get Form valueOF to do the comparison.. How?

  8. Hi All,
    I have a similar problem. i am using Calendar to get the date that is entered by the user in the format yyyy-MM-dd. But i am using the access database to store this. In access it accepts as dd-MON-yyyy.

    How do i convert the format suitable to access database.

    PLease provide a solution ASAP

  9. Hi all.
    i have a date in the form of a string .the format is 15-05-2005 5:55:55.
    Can any one tell how i can convert it to a sql.Date.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: