Search by date not working

Question

Search by date not working

Navigation

#1 by (1 votes)

-1

I am trying to search mySql database by filtering by date and it is not bringing any results. In the screen below I was able to search for the other criteria

Thisisthecodethatisdoingthesearches.

publicvoidmontaTabelaBusca(){Stringcriterio=" WHERE CLIENTE LIKE '" + inpClienteBusca.getText() + "%'"
            + " AND CARRO LIKE '" + inpCarroBusca.getText() + "%'"
            + " AND DESPESA LIKE '" + inpDespesaBusca.getText() + "%'"
            + " AND DATA LIKE '" + inpDataInicialBusca.getText() + "%'";

    ArrayList<Venda> Vendas = VendaDao.getResultadoDaVenda(criterio);
    String[] cabecalhoColunas = {"Id", "Data", "Cliente", "Carro", "Valor","Observação", "Despesa","Valor da despesa"};
    modeloTabela = new DefaultTableModel(cabecalhoColunas, 0);

    for (Venda v : Vendas) {
        String[] novaLinha = {String.valueOf(v.getId()),Formatar("dd/MM/yyyy", v.getData()),String.valueOf(v.getCliente()),String.valueOf(v.getCarro()), Numeros.Formatar("#0.00", v.getValor()),String.valueOf(v.getObservacao()),String.valueOf(v.getDespesa()),Numeros.Formatar("#0.00", v.getValorDespesa())};
        modeloTabela.addRow(novaLinha);
        jtResultadoDaBusca.setModel(modeloTabela);
    }
}

// getResultDownload

 public static ArrayList<Venda> getResultadoDaVenda(String condicao) {
    ResultSet resultado = ObjectFactory.getConexao().buscaSql("SELECT * FROM VENDA " + condicao);

    try {
        ArrayList<Venda> listagem = new ArrayList<>();
        while (resultado.next()) {
            Venda vendaPreenchida = preencheVenda(resultado);
            listagem.add(vendaPreenchida);
        }
        return listagem;
    } catch (SQLException ex) {
        System.out.println("não eoncontrado!");
    }
    return null;
}

mysql java date

asked by anonymous 16.04.2017 / 21:22

1 answer

Passing Date Field Parameters How to put InterstitialAD in the application?

score 1 · Accepted Answer

From what I understand, your problem is with the date format. The default format that MySQL uses to save date (DATE) is yyyy-mm-dd , ie if you do the search by directly typing the query into the search field, it goes search for something like "04/13/2017" and what is stored in the database is "2017-04-13" . If you only want to do full date searches containing day, month and year, you can use MySQL's STR_TO_DATE function to transform the search string into DATE in the query:

"AND DATA = STR_TO_DATE(" + inpDataInicialBusca.getText() + ", '%d/%m/%Y')"

Now, if you want to use LIKE to search for incomplete date strings, you can use MySQL's DATE_FORMAT function to format the "DATA" column to fit the format that you are using in the query, like this:

"AND DATE_FORMAT(DATA, '%d/%m/%Y') LIKE '" + inpDataInicialBusca.getText() + "%'"