Using setDate in PreparedStatement

Question

In order to make our code more standard, we were asked to change all the places where we hardcoded our SQL variables to prepared statements and bind the variables instead.

I am however facing a problem with the setDate().

Here is the code:

        DateFormat dateFormatYMD = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");         DateFormat dateFormatMDY = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");         Date now = new Date();         String vDateYMD = dateFormatYMD.format(now);         String vDateMDY = dateFormatMDY.format(now);         String vDateMDYSQL =  vDateMDY ;         java.sql.Date date = new java.sql.Date(0000-00-00);     requestSQL = "INSERT INTO CREDIT_REQ_TITLE_ORDER (REQUEST_ID," +                  " ORDER_DT, FOLLOWUP_DT) " +  "values(?,?,?,)";                   prs = conn.prepareStatement(requestSQL);                  prs.setInt(1,new Integer(requestID));                  prs.setDate(2,date.valueOf(vDateMDYSQL));                 prs.setDate(3,date.valueOf(sqlFollowupDT)); 

I get this error when the SQL gets executed:

    java.lang.IllegalArgumentException     at java.sql.Date.valueOf(Date.java:138)     at com.cmsi.eValuate.TAF.TAFModuleMain.CallTAF(TAFModuleMain.java:1211) 

Should I use setString() instead with a to_date()?

Answer 1

❐ Using java.sql.Date

If your table has a column of type DATE:

• java.lang.String

  The method java.sql.Date.valueOf(java.lang.String) received a string representing a date in the format yyyy-[m]m-[d]d. e.g.:

  ps.setDate(2, java.sql.Date.valueOf("2013-09-04")); 

• java.util.Date

  Suppose you have a variable endDate of type java.util.Date, you make the conversion thus:

  ps.setDate(2, new java.sql.Date(endDate.getTime()); 

• Current

  If you want to insert the current date:

  ps.setDate(2, new java.sql.Date(System.currentTimeMillis()));  // Since Java 8 ps.setDate(2, java.sql.Date.valueOf(java.time.LocalDate.now())); 

❐ Using java.sql.Timestamp

If your table has a column of type TIMESTAMP or DATETIME:

• java.lang.String

  The method java.sql.Timestamp.valueOf(java.lang.String) received a string representing a date in the format yyyy-[m]m-[d]d hh:mm:ss[.f...]. e.g.:

  ps.setTimestamp(2, java.sql.Timestamp.valueOf("2013-09-04 13:30:00"); 

• java.util.Date

  Suppose you have a variable endDate of type java.util.Date, you make the conversion thus:

  ps.setTimestamp(2, new java.sql.Timestamp(endDate.getTime())); 

• Current

  If you require the current timestamp:

  ps.setTimestamp(2, new java.sql.Timestamp(System.currentTimeMillis()));  // Since Java 8 ps.setTimestamp(2, java.sql.Timestamp.from(java.time.Instant.now())); ps.setTimestamp(2, java.sql.Timestamp.valueOf(java.time.LocalDateTime.now()));