java Exception : converting date and/or time from character string

Question

While executing below code getting conversation error message. Any help or suggestions really appreciate.

import java.sql.* ;
import java.util.logging.*;

class check
{
 public static void main( String args[] )
 {
  try
  {
      // Load the database driver
      Class.forName( "com.microsoft.sqlserver.jdbc.SQLServerDriver" ) ;

      // Get a connection to the database
      Connection conn = DriverManager.getConnection( "jdbc:sqlserver://example.com:1433;user=sa;password=root;databaseName=fetcher") ;

      // Print all warnings
      for( SQLWarning warn = conn.getWarnings(); warn != null; warn = warn.getNextWarning() )
      {
          System.out.println( "SQL Warning:" ) ;
          System.out.println( "State  : " + warn.getSQLState()  ) ;
          System.out.println( "Message: " + warn.getMessage()   ) ;
          System.out.println( "Error  : " + warn.getErrorCode() ) ;
      }

      // Prepare a statement
      String sql = "select top 5 ID, DN, Type, IPaddress, Date, CheckDate from DATA where (date >= getdate() -2 )";

      Statement cs = conn.createStatement();
      // Execute the query
      ResultSet rs = cs.executeQuery(sql) ;


      // Loop through the result set
      int i = 0;
      while( rs.next() ) {


         String row_id = rs.getString("ID");
         String row_dn = rs.getString("DN");
         int row_type = rs.getInt("Type");
         String row_ipaddress = rs.getString("IPaddress");
         Date row_date = rs.getDate("Date");
         Date row_checkdate = rs.getDate("CheckDate");
         iupdateQuery(i, row_id, row_dn, row_type, row_ipaddress,row_date, row_checkdate);
         i++;
         }

      // Close the result set, statement and the connection
      rs.close() ;
      cs.close() ;
      conn.close() ;

  }
  catch( SQLException se )
  {
      System.out.println( "SQL Exception:" ) ;

      // Loop through the SQL Exceptions
      while( se != null )
      {
          System.out.println( "State  : " + se.getSQLState()  ) ;
          System.out.println( "Message: " + se.getMessage()   ) ;
          System.out.println( "Error  : " + se.getErrorCode() ) ;

          se = se.getNextException() ;
      }
  }
  catch( Exception e )
  {
      System.out.println( e ) ;
  }
 }

 private static void iupdateQuery(int i, String row_id, String row_dn, int row_type, String row_ipaddress, Date row_date, Date row_checkdate) {
                String srow_id = row_id;
                String srow_dn = row_dn;
                int    srow_type = row_type;
                String srow_ipaddress = row_ipaddress;
                Date srow_date = row_date;
                Date srow_checkdate = row_checkdate;

                String squery =  "SET IDENTITY_INSERT dbo.DATA ON";
                String insertquery = "insert into DATA(ID, DN, Type, IPaddress, CheckDate) values ('"+ srow_id +"','"+ srow_dn +"','"+ srow_type +"','"+ srow_checkdate +"')"; 

                // debug
                String filename = "C:\\test\\output.txt";


                try {
                 // Load the database driver
                 Class.forName( "com.microsoft.sqlserver.jdbc.SQLServerDriver" ) ;

                // Get a connection to the database
               Connection dbconn = DriverManager.getConnection( "jdbc:sqlserver://localhost:1433;user=sa;password=root;databaseName=fetcher") ;

                        Statement es2 = dbconn.createStatement();
                        es2.executeUpdate(squery);   
                        int data_check = es2.executeUpdate(insertquery);
                        dbconn.commit();
                        if (data_check != 0)
                         {
                         System.out.println("Inserted: " + insertquery); 
                          }
                         else
                         {

                        String updatequery = "update DATA set DN='"+ srow_dn +"',Type='"+ srow_type +"',IPaddress='"+ srow_ipaddress +"',CheckDate='"+ srow_checkdate +"' where ID='"+ srow_id +"' "; 

                        // es2.executeUpdate(updatequery);
                        dbconn.commit();
                        System.out.println("Update: " + updatequery); 
                         }


                } catch (Exception e) {
                    // debug out output this way
                    System.err.println("Mysql Statement Error: " + insertquery);             
                    e.printStackTrace();

                }
        }


}

Error :

   com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converti
    ng date and/or time from character string.
            at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError
    (SQLServerException.java:197)
            at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServ
    erStatement.java:1493)
            at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQ
    LServerStatement.java:775)
            at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute
    (SQLServerStatement.java:676)
            at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
            at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLSe
    rverConnection.java:1400)
            at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLSer
    verStatement.java:179)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLS
erverStatement.java:154)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServ
erStatement.java:633)
        at syncdata.iupdateQuery(syncdata.java:129)
        at syncdata.main(syncdata.java:60)

check your SQL Server statement to get Date value instead of character value — Luiggi Mendoza
– Luiggi Mendoza, Commented Mar 18, 2012 at 16:52
Are you sure that rs.getDate("Date"); and rs.getDate("checkDate"); returns Date and not String. Whats the value type for Date and checkDate in database??? — Shashank Kadne
– Shashank Kadne, Commented Mar 18, 2012 at 16:58

Jon Skeet · Accepted Answer · 2012-03-18 17:11:17Z

1

Your code is somewhat tricky to read (and odd in terms of reporting an error from a SQLserver database as "Mysql Statement Error") - but basically both of these lines ought to change:

String insertquery = "insert into DATA(ID, DN, Type, IPaddress, CheckDate) values ('"
     + srow_id +"','"+ srow_dn +"','"+ srow_type +"','"+ srow_checkdate +"')"; 

...
String updatequery = "update DATA set DN='"+ srow_dn +"',Type='"+ 
         srow_type +"',IPaddress='"+ srow_ipaddress +"',CheckDate='"+ 
         srow_checkdate +"' where ID='"+ srow_id +"' ";

You should not include values in SQL statements like that. Instead, you should use a PreparedStatement and set parameters for the values. There are numerous benefits:

It avoids SQL injection attacks
It avoids conversion errors like the ones you've got at the moment
It keeps your code (the SQL) separate from your data
It can have performance benefits too in terms of query execution plan caching

See the JDBC tutorial for more informatoin.

answered Mar 18, 2012 at 17:11

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sush Over a year ago

Thanks jon, sure will use prepared statement

Shashank Kadne · Accepted Answer · 2012-03-18 17:18:15Z

0

The problem seems to be on this line...

String updatequery = "update DATA set DN='"+ srow_dn +"',Type='"+ srow_type +"',IPaddress='"+ srow_ipaddress +"',CheckDate='"+ srow_checkdate +"' where ID='"+ srow_id +"' ";

Here row_checkdateis of type Date and you are converting it into a String..

Internally when your database tries to convert this String to Date, it throws this error.

answered Mar 18, 2012 at 17:18

Shashank Kadne

8,1017 gold badges43 silver badges54 bronze badges

Collectives™ on Stack Overflow

java Exception : converting date and/or time from character string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related