31

I have a column in database having datatype DATETIME. I want to set this column value to current date and time using `PreparedStatement. How do I do that?

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
Param-Ganak
  • 5,787
  • 17
  • 50
  • 62

2 Answers2

57

Use PreparedStatement#setTimestamp() wherein you pass a java.sql.Timestamp which is constructed with System#currentTimeMillis().

preparedStatement.setTimestamp(index, new Timestamp(System.currentTimeMillis()));
// ...

Alternativaly, if the DB supports it, you could also call a DB specific function to set it with the current timestamp. For example MySQL supports now() for this. E.g.

String sql = "INSERT INTO user (email, creationdate) VALUES (?, now())";

Or if the DB supports it, change the field type to one which automatically sets the insert/update timestamp, such as TIMESTAMP instead of DATETIME in MySQL.

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
  • Hello Sir! As I mention the columns datatype in database is datetime so as in your solution you mention timestamp so please tell me is this solution will set current data and time to my datetime column? – Param-Ganak Oct 02 '11 at 11:38
  • Yes, I know that. What exactly is the problem you're having with it? The `java.sql.Timestamp` just represents the Java type of the MySQL `DATETIME` and `TIMESTAMP` field types. – BalusC Oct 02 '11 at 11:40
  • @BalusC, [in MySQL, those are not the same thing](http://stackoverflow.com/a/409305/963076). – ryvantage May 10 '14 at 17:10
  • @ryv: Where exactly did I said that they are the same? – BalusC May 10 '14 at 17:12
  • 1
    "The java.sql.Timestamp just represents the Java type of the MySQL DATETIME and TIMESTAMP field types" How could one object represent both types when they aren't the same? If you use `setTimestamp` and `getTimestamp`, does it muddle the data at all? Or are those methods sufficient to transfer the data whether your DB reflects a `DATETIME` or a `TIMESTAMP` ? – ryvantage May 10 '14 at 17:15
  • I guess I'm worried that the `get` and `set` method will mess with the data in some way... – ryvantage May 10 '14 at 17:15
  • @ryv: Uh, you're not supposed to set a `TIMESTAMP`. As answered, the DB does that automagically. For get, just use `java.sql.Timestamp` as hinted in a previous comment and implied in the answer. – BalusC May 10 '14 at 17:17
  • Sorry, I did not notice the OP was focusing on "current date/time," I was just figuring out how to set a MySQL `DATETIME` field in MySQL in general. I'm using jodatime `DateTime` class, so basically it's `p_stmt.setTimestamp(new java.sql.Timestamp(datetime.getTime()));`. Truth is I'm still quite confused wrapping my brain around the difference between `TIMESTAMP` and `DATETIME` anyways, so I figured since there was a difference, a singular `setTimestamp` method would be insufficient to handle both scenarios. – ryvantage May 10 '14 at 17:24
  • @ryv: Well, the difference is already explained in your first comment. I do still not understand why you posted that comment whereby you specifically target me though. – BalusC May 10 '14 at 17:25
  • @BalusC I have a date/time value in String form like "11/28/2017 4:00:49 PM". Now how can I take this same value to MySQL column of type datetime? – Suresh Dec 08 '17 at 11:12
1
conn = getConnection();
String query = "insert into your_table(id, date_column) values(?, ?)";
pstmt = conn.prepareStatement(query);
pstmt.setString(1, "0001");
java.sql.Date date = getCurrentDatetime();
pstmt.setDate(2, date);

Where the function getCurrentDatetime() does the following:

public java.sql.Date getCurrentDatetime() {
    java.util.Date today = new java.util.Date();
    return new java.sql.Date(today.getTime());
}
kinkee
  • 368
  • 2
  • 12