Using Setdate in Preparedstatement

Using setDate in PreparedStatement

❐ 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()));
  

Workaround setDate for PreparedStatement in Java

...but it seems like it does not work well since the Date classes methods are deprecated.

If you look at the JavaDoc's deprecation message, it tells you what to do:

Deprecated.

As of JDK version 1.1, replaced by Calendar.set(Calendar.YEAR, year + 1900).

(my emphasis on the second paragraph)

So the old way would be to:

• Get a Calendar using getInstance or one of its overloads.
• Use the methods of Calendar to set the correct date.
• Use the bizarrely-named getTime to get a Date instance for that date.
• Use the java.sql.Date constructor, passing in the Date's getTime().
• Use that with your PreparedStatement.

That said, with a modern JDK, you'd probably want to go through the java.time classes, probably LocalDate, to do the conversion instead as it's a fair bit simpler. Then you'd use java.sql.Date.valueOf(LocalDate):

ps.setDate(java.sql.Date.valueOf(LocalDate.of(
    tstlocaldate.year,
    tstlocaldate.month,
    tstlocaldate.dayOfMonth
));

JDBC Prepared Statement . setDate(....) doesn't save the time, just the date.. How can I save the time as well?

Instead of Date you should use a Timestamp and the setTimestamp method.

pratically you have to do something like that:

private static java.sql.Timestamp getCurrentTimeStamp() {
    java.util.Date today = new java.util.Date();
    return new java.sql.Timestamp(today.getTime());
}

....

preparedStatement.setTimestamp(4,getCurrentTimeStamp());

Query MySQL DB using preparedStatement.setDate

No, skip the Date part; simply use the string. Let's see the value of (String date ).

MySQL is happy if you can end up with ... tag_data_date = '2015-12-11'.

If String date looks like '2015-12-11', then the conversion to Date is unnecessary.

Oracle SQL PreparedStatement setdate

Try setting the date format explicitly with the SQL statements using TO_DATE, e.g.

String query = "INSERT INTO STUDENTS VALUES (?,?,?,TO_DATE(?,'YYYY-MM-DD'),?,?,?,?)";

Then ensure the date format in the Java variable matches. Change the format in the TO_DATE as required if different.

Inserting date in a database using PreparedStatement in Java

What error message?

Guessing that it's actually a compiler error message, are you sure you are using java.sql.Date and not java.util.Date?

Edit: As you edited question, yes you will need new java.sql.Date(date.getTime()) or something (data handling in Java is a mess! (at the moment)).

(JAVA) Need to convert DATE to prepared statement for SQL

You may use LocalDateTime along with PreparedStatement#setTimestamp(). Here is roughly how you would do that:

String birthDate = request.getParameter("data_birthdate");
// assuming your text birthdates look like 1980-12-30 00:30:05
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime dt = LocalDateTime.parse(birthDate, formatter);

try {
    connect = db.getConnect();
    ps = connect.prepareStatement(SQL_CreateUser);
    ps.setTimestamp(3, Timestamp.valueOf(dt));
}
catch (Exception e) {
    // handle exception
}

Note carefully the format mask being used in the call to DateTimeFormatter#ofPattern. You need to replace that with whatever mask actually fits your string dates.

---

Related Topics