Conversion failed when converting date or time from character string in java

Question

I have created two methods in java ,one of which returns the database server time and other returns the one hour ago time of database server.Now I have to use these two date time into a sql query.The code for retrieving database server time is:-

     public String database_Time() throws SQLException
       {

        con = getConnection();
        String sql = "select GETDATE()";
        clstmt = con.prepareCall(sql); 

        clstmt.execute();
        rs = clstmt.getResultSet();
        while(rs.next()) {
        timestr= rs.getString(1);
        }

        System.out.println("database time is" +timestr);

        return timestr;
       }

Another method for retrieving one hour ago time is

  public String previostime() throws ParseException, SQLException
    {   
     database_Time();

     String format = "yyyy-MM-dd HH:mm:ss";
      SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);
      Date date = simpleDateFormat.parse(timestr);
      Calendar calendar = Calendar.getInstance();
      calendar.setTime(date);
      int hours = calendar.get(Calendar.HOUR_OF_DAY);
      hours--;
      calendar.set(Calendar.HOUR_OF_DAY, hours);
      fixedDate = calendar.getTime();
      stringDate = simpleDateFormat.format(fixedDate );
     System.out.println("previous date is"+(stringDate));
     System.out.println("current date is"+timestr);
     return stringDate;
     }

But when I use stringDate and timestr in sql query ,then I got an error that com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.

Code for sql query is:- String sql = "select * from vs1_bag where logtime between 'stringDate'and 'timestr' ";

Edit:- Method through which I will retrieve the time,and will use it in my application is:-

public String [] getChartTime() throws SQLException, ParseException
    {   
    List<String> tStr = new ArrayList<String>();
    database_Time();
    String atime[] = null;
    previostime();
    getConnection();
    try
        {
             con = getConnection();

             stmt = con.createStatement();
  String sql = "select * from vs1_bag where logtime between 'stringDate'and 'timestr' ";
           stmt.executeQuery(sql);
           rs = stmt.getResultSet();

        while(rs.next()) {
            // Just get the value of the column, and add it to the list
            tStr.add(rs.getString(1).substring(11,16));
           }

        }
        catch( Exception e )
        {
            System.out.println("\nException in  Bean in getDbTable(String code):"+e);
        }


         finally
         {
            closeConnection();
         }
          // I would return the list here, but let's convert it to an array
     atime=  tStr.toArray(new String[tStr.size()]);

     return atime;


    }

How to resolve it.

Is logtime column datetime column? and use Prepare statements — Codeek
– Codeek, Commented Dec 2, 2014 at 4:27
If I'm not wrong you are getting error at "Date date = simpleDateFormat.parse(timestr);". I checked with SqlServer it gives "2014-12-02 09:56:46.870" on execution of "select GETDATE()". Try to test after removing ".870" means last microsecond part. — SeeTheC
– SeeTheC, Commented Dec 2, 2014 at 4:31
your sql query is wrong. see the answer and try it. tell me if it works — Codeek
– Codeek, Commented Dec 2, 2014 at 4:37

Codeek · Accepted Answer · 2014-12-02 04:40:04Z

2

shouldn't it be like this? :

String sql = "select * from vs1_bag where logtime between '"+stringDate+"' and '"+timestr+"' ";

edited Dec 2, 2014 at 4:40

answered Dec 2, 2014 at 4:29

Codeek

1,6341 gold badge11 silver badges20 bronze badges

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

5 Comments

SRY_JAVA Over a year ago

Its working now,thanks.Can you please tell me the sql query which I can write to retrieve logtime between current datetime and one hour ago time in sql server 2012.

Codeek Over a year ago

sorry, was not present here. you can use : String sql = "select * from vs1_bag where logtime between GETDATE() and DATEADD(hh,-1,GETDATE())";

SRY_JAVA Over a year ago

Thanks again,can I use this command directly in my string sql in the method where I have used +stringDate+"' and '"+timestr+"' .

SRY_JAVA Over a year ago

my one hour ago date is not being dispalyed when i'm using the above said sql string in java.Only current time is shown.

Codeek Over a year ago

Try putting it as a question referencing this one. give some codes. it'll be much easier to solve the issue if we could have code and expected output

Collectives™ on Stack Overflow

Conversion failed when converting date or time from character string in java

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related