13 Replies Latest reply: Apr 24, 2008 7:48 AM by 807601 RSS

    SQL JDBC Timestamp

    807601
      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
          Use a DateFormat object.
          • 2. Re: SQL JDBC Timestamp
            807601
            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
              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
                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
                  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
                    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
                      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
                        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
                          seems to code is correct. The error is due to imcompitable date formats.
                          • 10. Re: SQL JDBC Timestamp
                            791266
                            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
                              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
                                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
                                  Hello,

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

                                  Thanks all.