When using QBE, how can you search using a partial date?

gigabee · **Joined:** Sun Jun 05, 2005 9:45 am **Posts:** 27

When using Query By Example, how can I search on a part of a date?

For example: I have a Event object which has an event date. How can I find all objects with a given MM/DD? I appreciate your help.

Regards,

Joshua

pksiv · **Posted:** Tue Feb 14, 2006 12:37 pm

gigabee wrote:

When using Query By Example, how can I search on a part of a date?

For example: I have a Event object which has an event date. How can I find all objects with a given MM/DD? I appreciate your help.

Regards,

Joshua

I doubt that you can if the field you are searching is a date field. You're "example" wouldn't be able to contain a valid date.

gigabee · **Joined:** Sun Jun 05, 2005 9:45 am **Posts:** 27

There has to be a way to search by a partial date. Granted the criteria is not usually MM/DD, I am sure people have needed to search by just the year.

Any ideas?

Regards,

Joshua

pksiv · **Posted:** Tue Feb 14, 2006 3:32 pm

gigabee wrote:

There has to be a way to search by a partial date. Granted the criteria is not usually MM/DD, I am sure people have needed to search by just the year.

Any ideas?

Regards,

Joshua

Sure you can, just not with QBE. Critiria queries allow you do search by dates using > < = or between two ACTUAL dates.

gigabee · **Joined:** Sun Jun 05, 2005 9:45 am **Posts:** 27

Ok,

so if QBE is out, the partial date problem still exists. How would you do it?

pksiv · **Posted:** Tue Feb 14, 2006 9:19 pm

gigabee wrote:

Ok,

so if QBE is out, the partial date problem still exists. How would you do it?

Using Criteria queries assuming you have a mapped object with a Date attribute named myDateAttribute.

For the between case, you simply construct from a startDate of 01/01/YYYY for what ever year you're dealing with and an endDate of 12/31/YYYY for the same year.

Code:

criteria.add(Restrictions.between("mydateAttribute", startDate, endDate))

criteria.add(Restrictions.ge("mydateAttribute", startDate))

criteria.add(Restrictions.lt("mydateAttribute", endDate))

jlhuertas · **Joined:** Wed Mar 15, 2006 9:15 am **Posts:** 2

pksiv wrote:

For the between case, you simply construct from a startDate of 01/01/YYYY for what ever year you're dealing with and an endDate of 12/31/YYYY for the same year.

Code:

criteria.add(Restrictions.between("mydateAttribute", startDate, endDate))

criteria.add(Restrictions.ge("mydateAttribute", startDate))

criteria.add(Restrictions.lt("mydateAttribute", endDate))

OK, but what if the criteria is about the month or the day? For example... "give me all the products bought in June (no matter the day or the year)"

I don't know how to do it... maybe mixing Criteria with plain SQL? But it doesn't seem very elegant... :(

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

if your database support like for date you can do it with simple LIKE condition (and string parameter in condition).
(oracle support like for date and number, for example)

wemmedge · **Posted:** Thu Jan 18, 2007 5:07 am

Hi,

do get entries out of a table on the DB, where the date is in one month, you pursue the following:

Code:

Calendar start_june= new GregorianCalendar();
Calendar end_june= new GregorianCalendar();
// Set the start and end dates to first of June and 30th

int year= 2007;   // e.g.

start_june.set(year, Calendar.JUNE,  1);
end_june.set(year, Calendar.JUNE, 30);

Criteria criteria = getSession().createCriteria(MyDomainTable.class);

...

criteria= criteria.add(Restrictions.ge("birthday", start_june.getTime()));
criteria= criteria.add(Restrictions.le("birthday", end_june.getTime()));

So easy :)