asked on

EJB QL - selecting current year in date type

Hi Experts,

I am working on jboss seam web app and I need to modifiy ejb ql so that I can use same code next year without modifying code.

select qualReport from QualReport qualReport where qualReport.countyName='cherokee' order by qualReport.quarter, qualReport.rowOrder

The above works fine, but I want to select rows only for current year. There is a createdDate column that stores oracle Date when rows were inserted and there are some rows that were created this year.

thanks,

anilallewar

This is going to be dependent on what format dates are stored in oracle. On mySQL, I was able to get this to return rows for the current year only. Note that within mySQl dates are stored in the format "2010-02-17 16:15:20"

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Calendar calObj = new GregorianCalendar(new GregorianCalendar().get(Calendar.YEAR), Calendar.JANUARY, 1);

logger.debug("The calender corresponding to first of the year is: " + format.format(calObj.getTime()));
criteria = em
.createQuery("SELECT billingDetails FROM CreditCard as billingDetails where billingDetails.created > '" + format.format(calObj.getTime()) + "'");

Sathish David Kumar N

you can user Year(GetDate()) in the query then you will get the year of the date

Sathish David Kumar N

example

"SELECT billingDetails FROM CreditCard as billingDetails where billingDetails.created > Year(GetDate())"

ASKER CERTIFIED SOLUTION

anilallewar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial