Help with Date and Time Search

Asked

Viewed 358 times

0

Good Morning

I’m doing a date search on android and I’m having trouble checking the time.He says it gave syntax error near the time, and another when I pick the date the time is showing less 3 hours. Can anyone help me? follow the code the time format I pick up at the bank is this: 2016-11-23 16:34:37,000

public String nomeTabela()  {
    String Pesquisa = "";
    ConexaoDao conexao = new ConexaoDao();
    ObjetoConexao objConexao = new ObjetoConexao();
    objConexao.db_connect_string = "flexvale.hopto.org:1433";
    objConexao.db_name = "FlexPortaCom";
    objConexao.db_userid = "sa";
    objConexao.db_password = "flextelecom";
    Connection conn = conexao.dbConnect(objConexao);

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
    String currentDateandTime = sdf.format(new Date () );
    System.out.println(currentDateandTime);

    if (conn == null) {

        Pesquisa = "Não foi possivel se conectar ao banco de dados";

    } else



    if (conn != null) try {


        Statement statement = conn.createStatement();

        String queryString =  " Select SUM(DIFERENÇA)as somaMes from TOTALIZADOR WHERE  NID = 252 and DATAHORA  >= "+currentDateandTime+" and DATAHORA = "+currentDateandTime+" ";
        ResultSet rs;



        rs = statement.executeQuery(queryString);

        if (rs.next()) {

            Pesquisa = rs.getString("somaMes");

        }
    } catch (SQLException e) {
        Pesquisa = e.getMessage();


    }
    return Pesquisa ;




}
java android

  • Puts the exact error in your question

  • Sorack when I do the search without the date for example 2016-11-23 will work well the search but when I put 2016-11-23 16:34:37.000 the search returns syntax error close to 16. which is the beginning of the hours. I need the hours because I need to do several searches, returning data from the last 10 minutes, 1 hour, etc

  • What is the format of the field DATE on the table ?

  • Thiago the format is this - 2016-11-23 16:34:37,000 when I do the search without the hours it returns me empty even having data and when I put on time it gives a syntax error close to the first of the hours.

  • the field is datetime

  • and this is my search, I need to take the sum of the field between the dates " Select SUM(DIFFERENCE)as somaMes from TOTALIZER WHERE NID = 253 and DATAHORA BETWEEN "+Data1+" and "+Data2+" ";

Show 1 more comment

1 answer

1

Although it is possible to execute an SQL query using text concatenation, do not, because it is putting your system at risk by allowing SQL injection.

Use a PreparedStatement and pass the date parameters using the method setDate().

Example:

private static java.sql.Date getCurrentDate() {
    java.util.Date today = new java.util.Date();
    return new java.sql.Date(today.getTime());
}

public String nomeTabela()  {

    ...

    String insertTableSQL = "INSERT INTO DBUSER"
        + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
        + "(?,?,?,?)";
    preparedStatement = dbConnection.prepareStatement(insertTableSQL);    
    preparedStatement.setDate(4, getCurrentDate());

    ...

}

Note: if you really want to concatenate a literal date, the first step is to quote before and after, after all there is a space between date and time. Of all the banks I know, some accept a text literal and make the implicit conversion to a date. However, in this case you will probably need to use a function to explicitly convert the string to date/time.

Browser other questions tagged java android

You are not signed in. Login or sign up in order to post.