I want to display all users that have their birthday within the current week. The birthday field is mapped with a nullable datetime object.

I would now need a query like
"FROM Users WHERE Birthday.HasValue AND Day(Birthday.Value) BETWEEN :dayOfYearBegin AND :dayOfYearEnd"

Unfortunately this does not work. Has somebody a solution for my problem (I think that this is should be a common issue)?

Use SQL syntax: where Birthday is not null and day(Birthday) between ...

You need to transpose all bithdays to the same year in order to fetch then with a "between" statement

So if i am born on 1.2.1974 (DMY) and my brother is born on 3.2.1978 we are both most likely born in the same "week" but if you try to fetch this data with a "between" then you are going to have some troubles.

But using the "day(Birthday.Value)" is also something you should avoid. Its bad practice to supply a formula to the left side of where clause, since this formula will be executed for every row of data (-> Index or table scan).

Depending on your DBMS you could either use a calculated field with an index on it, or you could split up the birthday information into 2 fields (Day/Month and year) of you could suppy a view that does this mapping automatically. This would allow a nice between query for the year "0" (And since you also need "month" it will save you 2 function calls for every row of data).

Also, as an added bonus, you can then use "simple" HQL to querry the data ;)

Thanks for your answers.
Summarized HQL does not support this functionality and I should use raw SQL instead?

I'm using SQL-Server so the function datepart(dy, Birthday) is in my opinion a good solution.