EJB QL - selecting current year in date type

Posted on 2010-03-25
Medium Priority
Last Modified: 2013-11-11
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.

Question by:dkim18
  • 2
  • 2
LVL 11

Expert Comment

ID: 28666220
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()) + "'");
LVL 20

Expert Comment

by:Sathish David Kumar N
ID: 28948447
you can user Year(GetDate()) in the query then you will get the year of the date
LVL 20

Expert Comment

by:Sathish David Kumar N
ID: 28948642

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

Accepted Solution

anilallewar earned 2000 total points
ID: 28960136
With oracle it would be something like this

"SELECT billingDetails FROM CreditCard as billingDetails where billingDetails.created > TRUNC(SYSDATE, 'YEAR')"

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues undeā€¦
A solution for Fortify Path Manipulation.
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
The viewer will learn how to implement Singleton Design Pattern in Java.
Suggested Courses
Course of the Month3 days, 23 hours left to enroll

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question