Title: Cannot store Date variable in database in Java
Tags: java;database;jsp;jakarta-ee;servlets
Question: I am trying to keep the last login date of a user in the database. Here are the necessary parts:

In my login page, I create a Date object and put it into my session, I import java.util.Date:

```<% 
Calendar c = Calendar.getInstance();

Date l = new Date(c.get(Calendar.YEAR),c.get(Calendar.MONTH),c.get(Calendar.DAY_OF_MONTH),c.get(Calendar.HOUR),c.get(Calendar.MINUTE)); //the midnight, that's the first second of the day.


//Globals.customer.lastlogin=l;
session.setAttribute("lastlogin", l);   %&gt;
```

Then when login is successful, I update my database in my servlet, this servlet is called when login button is clicked:

```try {
    int result=-1;
    PreparedStatement checkDB = (PreparedStatement) con.prepareStatement(
            "UPDATE users set lastlogin='"+(Date)session.getAttribute("lastlogin")+"' where username='"+session.getAttribute("username")+"'");


        result= checkDB.executeUpdate();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
```

I keep lastlogin as a Date variable in my Database. But I get the following error when trying to update last login:

```com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: 'Wed Jul 02 05:49:00 VET 3913' for column 'lastlogin' at row gutierrezgeorge@example.net(MysqlIO.java:2936)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1601)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1710)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2436)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1402)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1694)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1608)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1593)
at classes.LoginServlet.service(LoginServlet.java:94)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:662)
```

Can anyone help me with this? Thanks

Comment: May be this can help - http://stackoverflow.com/questions/530012/how-to-convert-java-util-date-to-java-sql-date


Here is another answer: You should take advantage of the PreparedStatement and use ? placeholders for your parameters.

```PreparedStatement checkDB = (PreparedStatement) con.prepareStatement(
                "UPDATE users set lastlogin=? where username=?");

checkDB.setDate(1, (Date)session.getAttribute("lastlogin"));
checkDB.setString(2, session.getAttribute("username"));
```

That will format the date correctly as required by your database.

Comment for this answer: Thanks, but by using java.sql.date i cannot store hours and minutes is there any way to do this?
Comment for this answer: Use java.sql.Timestamp to store date with time. `java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(utilDate.getTime());` and then use `checkDB.setTimestamp` instead of `checkDB.setDate`.

Here is another answer: ```try {
    int result=-1;
     String dateToInsert = new SimpleDateFormat(yyyy-MM-dd).parse((Date)session.getAttribute("lastlogin"));
    PreparedStatement checkDB = (PreparedStatement) con.prepareStatement(
            "UPDATE users set lastlogin='"+dateToInsert+"' where username='"+session.getAttribute("username")+"'");


        result= checkDB.executeUpdate();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
```

Try with this .
The problem was you have date object in your session in the format that cannot insert into D.B. The date should be in format that compatible with D.B


Here is another answer: You should use a question mark to parametrise PreparedStatements.

Try this (Make sure the Date is java.sql.Date):

```String getUserLastLogin = "UPDATE users set lastlogin=? where username=?";
Date lastLogin = (Date)session.getAttribute("lastlogin");
String username = session.getAttribute("username");

PreparedStatement checkDB = (PreparedStatement) con.prepareStatement(getUserLastLogin);
checkDB.setDate(1, lastLogin);
checkDB.setString(2, username);

result= checkDB.executeUpdate();
```

Also, add a finally block after the catch and make sure you close your PreparedStatement, and then your Connection so you don't leave open database connections.

Comment for this answer: Use the Calendar, and then convert it to a date when you need to store it in the database.
Comment for this answer: Sorry, you should be using java.sql.Timestamp.
Comment for this answer: Thanks, but by using java.sql.date i cannot store hours and minutes is there any way to do this?
Comment for this answer: yes, but this can be done in java.util.date, java.sql.date has no proper constructors for this, or i do not know

Here is another answer: you need to take the ```java.sql.timestamp``` to get the time as well ...


Here is another answer: You may need to check whether you are using java.sql.Date or java.util.Date. Use java.sql.date to avoid any compatibility issues.

Comment for this answer: @constructor If you can, then consider stroring the time in milliseconds in DB and format it when you read it.
Comment for this answer: i am using java.util.date, but when i use sql.date i cannot store minutes and hour right? How can i do it?

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 5, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2013:00:07/02/13', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<java><database><jsp><jakarta-ee><servlets>', 'Title': 'Cannot store Date variable in database in Java', 'ViewCount': 910, 'id': 17423247, 'user_AboutMe': None, 'user_AccountId': None, 'user_DisplayName': None, 'user_Reputation': None, 'user_Views': None, 'user_id': None}}