Main Tutorials

Insert date value in PreparedStatement

Problem

A simple table script in Oracle database.


CREATE TABLE DBUSER ( 
  USER_ID       NUMBER (5)    NOT NULL, 
  USERNAME      VARCHAR2 (20)  NOT NULL, 
  CREATED_BY    VARCHAR2 (20)  NOT NULL, 
  CREATED_DATE  DATE          NOT NULL, 
  PRIMARY KEY ( USER_ID ) 
 )

No idea how to insert current date value, e.g. “04/04/2011” into “CREATED_DATE” field, via JDBC PreparedStatement.


String insertTableSQL = "INSERT INTO DBUSER"
		+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
		+ "(?,?,?,?)";
preparedStatement = dbConnection.prepareStatement(insertTableSQL);
preparedStatement.setDate(4, ???);

Solution

The “preparedStatement.setDate()” method is accept a java.sql.Date parameter, so, you have to convert from java.util.Date to java.sql.Date.

For example, create a method to return current date, and convert it java.sql.Date :


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

And set the returned date via preparedStatement.setDate().


String insertTableSQL = "INSERT INTO DBUSER"
	+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
	+ "(?,?,?,?)";
preparedStatement = dbConnection.prepareStatement(insertTableSQL);
preparedStatement.setDate(4, getCurrentDate());

Done.

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
11 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Manjul
7 years ago

String testdate=”20/06/2016″;
PreparedStatement stmt = con.prepareStatement(
“INSERT INTO records ”
+ “(id, name, birthday)”
+ “VALUES(?,?,STR_TO_DATE(?,’%Y/%m/%d’))”);
stmt.setString(1, “EN123”);
stmt.setString(2, “Prabhat”);
stmt.setString(3, testdate);
stmt.executeUpdate();

ajit
11 years ago

sir i hav a doubt
can u pls help me
pls tellme how to auto increment rollno field no for training n placement db
what value shoud i pas from jsp page using servelet

akhil sai
3 years ago

please help me i am cant able to convert the string into date using database connection
DateFormat formatter ;
formatter = new SimpleDateFormat(“yyyy-MM-dd”);
Date date=(Date) formatter.parse(bean1.getEmpdate());

System.out.println(“Employee date is presented=” + formatter.format(date));

cstmt.setDate(4, (Date) date);

Tan Wee Teck
4 years ago

if (variable.getText().trim().isEmpty())
{ JOptionPane.showMessageDialog(null,”Please key-in the Data, Please.. \n”
+ “Thank you. “);
return false;}

hi, above is for if text field is empty.
may i know how does we do in date when using jdatechooser?
please.

Furqan
6 years ago

what if we have date in string what to do instead of SYSDATE / getCurrentDate

testUser
8 years ago

can we set SYSDATE using setDate or setRef in prepared statement.

Anyul Rivas
10 years ago

I tried doing as explained, but every time i check the prepared Statement it passes the date value as string with unknown data type (0)

Manwendra
10 years ago

Hi mkyong thanx for the tutorial it is really useful. I am having a problem: when I insert the current date it stores 1970-01-01 in mysql database always

Manwendra
10 years ago
Reply to  Manwendra

i m sorry it was my fault, didn’t follow the steps in correct order. its working now thanks

praveen
10 years ago

Im getting a com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: ‘1999’ for column…. when im trying to insert a java.util.date value.

kishor
10 years ago

Nice one!! The code snippet is useful. Keep posting such stuff.