This discussion is archived
13 Replies Latest reply: Apr 24, 2008 5:48 AM by 807601 RSS

SQL JDBC Timestamp

807601 Newbie
Currently Being Moderated
Hello all,

How to convert java.Sql. timestamp yyyy-mm-dd HH:MM:SS.nanosec to yyyy-mm-dd HH:MM:SS format.

Thanks
  • 1. Re: SQL JDBC Timestamp
    807601 Newbie
    Currently Being Moderated
    Use a DateFormat object.
  • 2. Re: SQL JDBC Timestamp
    807601 Newbie
    Currently Being Moderated
    Try something like this:
    Date date = new Date();
    
    Format formatter = new SimpleDateFormat("yyyy.MM.dd.HH.mm.ss");
    String s = formatter.format(date);
  • 3. Re: SQL JDBC Timestamp
    807601 Newbie
    Currently Being Moderated
    My code is:-

    String cDt = "SELECT NOW()";

    ResultSet curdate = st.executeQuery(cDt);

    while(curdate.next()){
    cdt = curdate.getTimestamp(1);
    }


    String updateString = "UPDATE `1` SET startdate = 'cdt' " +
    "WHERE idchecklist LIKE " + chkId;
    st.executeUpdate(updateString);

    The output is 2008-04-25 18:28:00.0

    Unable to insert into a Mysql Database to a filed which is timestamp. Pls provide me how to convert this to MYSQL format.
  • 4. Re: SQL JDBC Timestamp
    791266 Explorer
    Currently Being Moderated
    shreenivasa wrote:
    My code is:-

    String cDt = "SELECT NOW()";

    ResultSet curdate = st.executeQuery(cDt);

    while(curdate.next()){
    cdt = curdate.getTimestamp(1);
    }


    String updateString = "UPDATE `1` SET startdate = 'cdt' " +
    "WHERE idchecklist LIKE " + chkId;
    st.executeUpdate(updateString);

    The output is 2008-04-25 18:28:00.0

    Unable to insert into a Mysql Database to a filed which is timestamp. Pls provide me how to convert this to MYSQL format.
    You should use PreparedStatement.
  • 5. Re: SQL JDBC Timestamp
    807601 Newbie
    Currently Being Moderated
    now is statment execution is working because of date format unable to update. because select NOW() output is YYYY-MM-DD HH:MM:SS.nanosec But it should be YYYY-MM-DD HH:MM:SS for mysql database.

    How to remove this nanoseconds?
  • 6. Re: SQL JDBC Timestamp
    791266 Explorer
    Currently Being Moderated
    shreenivasa wrote:
    now is statment execution is working because of date format unable to update. because select NOW() output is YYYY-MM-DD HH:MM:SS.nanosec But it should be YYYY-MM-DD HH:MM:SS for mysql database.

    How to remove this nanoseconds?
    You don't need to do it. You should use PreparedStatement (as I previously said). The driver will handle the conversion for you.

    Kaj
  • 7. Re: SQL JDBC Timestamp
    807601 Newbie
    Currently Being Moderated
    Now I cahnged code like this:

    String cDt = "SELECT NOW()";

    ResultSet curdate = st.executeQuery(cDt);


         while(curdate.next()){
    cdt = curdate.getTimestamp(1);
    }



    String updateString = "UPDATE `1` SET startdate = 'cdt' " +
    "WHERE idchecklist LIKE " + chkId;
    PreparedStatement up = con.prepareStatement(updateString );
    up.executeUpdate();

    But there is an error:

    com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '
    cdt' for column 'startdate' at row 2

    Any suggestions?
  • 8. Re: SQL JDBC Timestamp
    791266 Explorer
    Currently Being Moderated
    String updateString = "UPDATE `1` SET startdate = 'cdt' " +
    "WHERE idchecklist LIKE " + chkId;
    PreparedStatement up = con.prepareStatement(updateString );
    up.executeUpdate();
    That's not how you should use a PreparedStatement. Read the javadoc, and look at the example.

    Kaj
  • 9. Re: SQL JDBC Timestamp
    807601 Newbie
    Currently Being Moderated
    seems to code is correct. The error is due to imcompitable date formats.
  • 10. Re: SQL JDBC Timestamp
    791266 Explorer
    Currently Being Moderated
    shreenivasa wrote:
    seems to code is correct. The error is due to imcompitable date formats.
    It isn't. You aren't using the PreparedStatement in the correct way. I'm not guessing.

    Did you read the javadoc for PreparedStatement? Did you look at the example?
  • 11. Re: SQL JDBC Timestamp
    807601 Newbie
    Currently Being Moderated
    No. Syntax is correct when I tried to put date manually like this:-

    String updateString = "UPDATE `1` SET startdate = '2008-12-03 00:12:00' " +
    "WHERE idchecklist LIKE " + chkId;

    It works!!.

    correct me if i am wrong.
  • 12. Re: SQL JDBC Timestamp
    791266 Explorer
    Currently Being Moderated
    shreenivasa wrote:
    No. Syntax is correct when I tried to put date manually like this:-

    String updateString = "UPDATE `1` SET startdate = '2008-12-03 00:12:00' " +
    "WHERE idchecklist LIKE " + chkId;

    It works!!.

    correct me if i am wrong.
    Haven't I done it 3 times now? you are wrong.
  • 13. Re: SQL JDBC Timestamp
    807601 Newbie
    Currently Being Moderated
    Hello,

    Yes. you are right!! there is a mistake!! I corrected it. worked fine.

    Thanks all.