Need Query: Birthday next x days

QStorm · **Joined:** Thu Jun 08, 2006 5:32 pm **Posts:** 52

Iam searching for the following query:

I have an entity "Contact" which have a birthday (Date). I need a query that results all contacts which have brithday the next x days. x stands for a integer, 14 for example.

Have someone an idea? I have some trouble with the 'year' part of the birthday. I don't know if the criteria API is the better way to create this kind of query.

Thanks
QStorm

thhart · **Joined:** Sun Aug 01, 2004 6:49 pm **Posts:** 76

1. Ugly: You can try to convert your date to a timestamp. This is in general a milliseconds value since 01.01.1970. In every DBMS you have functions for that. You can query for something that is x * 24 * 3600 * 1000 ahead.
2. Nice: Construct a date in you language and set it to x days ahead. Compare this via criteria language or a special query. Every DBMS does support date comparisons as well.

HTH
Thomas

QStorm · **Joined:** Thu Jun 08, 2006 5:32 pm **Posts:** 52

I tried to create a special query. My problem is the year part of the date. I couldn't find an year independent query.

thhart · **Joined:** Sun Aug 01, 2004 6:49 pm **Posts:** 76

Post your code.

hardy.ferentschik · **Posted:** Mon Apr 06, 2009 6:36 am

Hi,

this seems to be a HQL or JPAQL specific question. If so you are better of asking it on the Hibernate User forum.

If you are using Hibernate Search and want help with a Lucene query provide some more detail about your code.

--Hardy

QStorm · **Joined:** Thu Jun 08, 2006 5:32 pm **Posts:** 52

I use the following query:

Code:

SELECT * FROM Contact c WHERE c.birthday >= '2009-04-06' AND c.birthday <= '2009-04-20';

I need a year independent query. I have try to use wildcards (%, _) but without success. These wildcards are only available for strings and "LIKE". The result must be contains all contacts with birthday between 04-06 and 04-20 (any year).

thhart · **Joined:** Sun Aug 01, 2004 6:49 pm **Posts:** 76

1. Set the correct date/year in your application and construct the query string from that.
2. Please see date functions for you DBMS. To get the current year. Something like year(now()). You can then contsruct a valid date from that.

You have to post your code. The query itself is not the code. Please post all the code arround it.

QStorm · **Joined:** Thu Jun 08, 2006 5:32 pm **Posts:** 52

1. This is impossible I think.
Example:

1983-09-12
1970-04-10
1990-04-23

How to set the correct date / year for all birthdays between 04-09 and 04-25?

2. I will check this. But an DBMS related solution is quit ugly (if the DBMS must be changed).

QStorm · **Joined:** Thu Jun 08, 2006 5:32 pm **Posts:** 52

I have found a query:

Code:

SELECT c FROM Contact c WHERE DAYOFYEAR(curdate()) <= dayofyear(c.birthday) AND DAYOFYEAR(curdate()) +7 >= dayofyear(c.birthday)

Thanks for help
QStorm

dan8ny · **Joined:** Wed Oct 27, 2010 3:12 pm **Posts:** 2

Does that query work when the birthday is on january 1st and the current date is december 31st? I don't think so.

dan8ny · **Joined:** Wed Oct 27, 2010 3:12 pm **Posts:** 2

If you form a number of the dates of the format YearMonthDay (so 20101028 for today) you can compare the dates easily. So, take the current year and concat it to the birthday month and day. If the birthday already passed, add 10000 (1 year). Then turn each end of the range of dates into the YearMonthDay number. Checking if the new birthday number falls between or on these numbers will tell you if the birthday is in the range.

Here's an example:
Birthday: 1/1/1980
Begin Date Range: 12/31/2010
End Date Range (1 week): 1/7/2011

Turn the birthday into 20100101
Check if the birthday is before the begin date range of 20101231. Since it is, add 10000 so birthday becomes 20110101.
Check if birthday (20110101) >= begin date (20101231) and birthday <= end date (20110701). It is, so birthday is included.