How to compare Date with field in MySQL

3

I'm new to Java and I have a table in MySQL that has a field of type date and dates are stored in yyyy-mm-dd format.

Now I need to retrieve only the records whose date field value is greater than or equal to today's date.

I'm trying to do the below, but it's not working:

Date dataHoje = new Date();
Query consulta = em.createQuery("SELECT c FROM Consulta c WHERE c.dtConsulta >= dataHoje");

Could someone help?

Excuse me if I did not post the code completely, but it follows below:

public List<Consulta> listarDatasLivresParaMarcarConsultas(int mes, int ano){

    em.getTransaction().begin();

    Date dataHoje = new Date();

    Query consulta = em.createQuery("select c from Consulta c " +
            "left outer join fetch c.terapeuta t " + 
            "left outer join fetch c.cliente cl " + 
            "SUBSTRING(c.dtConsulta, 1, 4) = ?1 AND " + 
            "SUBSTRING(c.dtConsulta, 6, 2) = ?2 AND " +
            "c.dtConsulta >= dataHoje AND " +
            "c.cliente = null AND " +
            "c.statusConsulta = 'C'");

        consulta.setParameter(1, ano);
        consulta.setParameter(2, mes);

        List<Consulta> consultas = consulta.getResultList();

        em.getTransaction().commit();
        emf.close();

        return consultas;
    }

The user chooses the month and year through JMonthChooser and JYearChooser components, which return two integers respectively.

Thanks for any help !!! Augusto

    
asked by anonymous 25.06.2014 / 23:19

1 answer

3

You can do this in the SQL query itself, for example:

SELECT * FROM consulta t
WHERE t.dtconsulta >= curdate();

Here's a minimalist example

    
25.06.2014 / 23:32