How to query selected Months records

zed420 · **Joined:** Wed Aug 18, 2010 6:43 am **Posts:** 10

Hi All

Can anyone please tell me how to query for a selected month's records. I have a query below that selects all records of current month but I need some thing which fetches all records of selected month (as a Parameter).

Code:

@NamedQuery(name = "Invoice.findByMonth", query = "SELECT  i FROM Invoice i WHERE month(to_date(to_char(i.invoiceDate, 'DD-Mon-YYYY'))) = month(to_date(to_char(sysdate, 'DD-Mon-YYYY')))"),

Many thanks

Zed

mmerder · **Posted:** Thu Mar 24, 2011 9:48 am

Code:

SELECT i FROM Invoice i WHERE month(i.invoiceDate) = :month

This query selects all Invoices for a given month of year (for all years).

zed420 · **Joined:** Wed Aug 18, 2010 6:43 am **Posts:** 10

Thank you mmerder that has worked, this is how I used it. I'm sure there was a better way of doing it in BEAN but this is how I did it.

Code:

@NamedQuery(name = "Invoice.findByInvoiceForWholeMonth", query = "SELECT  i FROM Invoice i WHERE month(i.invoiceDate) = :month")

Code:

public List<Invoice> getInvoiceByWholeMonth(Integer month) {
      em.getTransaction().begin();
      List<Invoice> invDate = em.createNamedQuery("Invoice.findByInvoiceForWholeMonth")
      .setParameter("month", month).getResultList();
      em.getTransaction().commit();
      return invDate;
   }

In Bean

Code:

String string= invoiceObj.getInvoiceDate().toString();
        String d = string.substring(6, 7);
        int date = Integer.parseInt(d) ;
      invoiceList = hibernateQueryManagerDao.getInvoiceByWholeMonth(date);   

Many Thanks
Zed