FabioWidmer FabioWidmer - 3 months ago 13
SQL Question

Hibernate Query Compare Date (Max. 10 months in past)

I'm trying to find out if the date is within 10 months. If not the record should be ignored! The solution needs to work with HQL. I already tried multiple queries:

This one is working but not what I want:

session.createQuery("FROM Application WHERE id="+id+" AND date < current_date()").getResultList();


Something is null here:

session.createQuery("FROM Application WHERE id="+id+" AND month(date) + (10-month(current_date()) < month(current_date())").getResultList();


"Interval" is unknown:

session.createQuery("FROM Application WHERE id="+id+" AND DATE_SUB(current_date(), INTERVAL 10 MONTH) < date").getResultList();


"Months" is unknown:

session.createQuery("FROM Application WHERE id="+id+" AND current_date()-10 month < date+").getResultList();


I don't have more ideas how to solve this problem. Hopefully someone can help me here.

Thanks!

Answer

Why dont you try this...

    static SimpleDateFormat datetimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    Calendar date = Calendar.getInstance();
    date.add(Calendar.MONTH, -10);
    Date date1=date.getTime();
    String newdate=datetimeFormat.format(date1);

   Query query=session.createQuery("FROM Application a WHERE a.id="+id+" AND a.date <" + "'"+newdate+"'");

   System.out.println(query.list().size());
Comments