2

I have a table which contains a column Birthday of Type DateTime. Now I should select with HQL all Persons which Birthday is in the next 10 days or was in the last 5 days. How can I do this with NHibernate 3.2 HQL? Thanks. Thomas

heisenberg
  • 9,665
  • 1
  • 30
  • 38
BennoDual
  • 5,865
  • 15
  • 67
  • 153

2 Answers2

2

I have solved it with

var result =
session.CreateQuery(@"from Person 
                      where 1 = (FLOOR(DATEDIFF(dd,Birthday,GETDATE()+10) / 365.25))
                                    -
                                (FLOOR(DATEDIFF(dd,Birthday,GETDATE()-5) / 365.25))")
       .List<Person>();
BennoDual
  • 5,865
  • 15
  • 67
  • 153
0

In HQL one way is:

Session.CreateQuery("FROM PersonTable WHERE Birthday <= :todayPlusTenDays AND Birthday >= :todayLessFiveDays")
.SetParameter(":todayPlusTenDays", DateTime.Today.AddDays(10))
.SetParameter(":todayLessFiveDays", DateTime.Today.AddDays(-5))

Alternatively, however i am unsure if the between is inclusive or not off the top of my head:

Session.CreateQuery("FROM PersonTable WHERE Birthday BETWEEN :todayLessFiveDays AND :todayPlusTenDays")
    .SetParameter(":todayPlusTenDays", DateTime.Today.AddDays(10))
    .SetParameter(":todayLessFiveDays", DateTime.Today.AddDays(-5))
TheITGuy
  • 722
  • 4
  • 15
  • Thanks, but the problem with this solution is, that the birtday has different year as today so it will only find persons which are just born or will be born in the next 10 days. Do you have an idea how I can select without regarding the year? – BennoDual Jun 28 '11 at 21:34
  • Very good point, i knew it was too early for me to post replies without my coffee :) Not too sure about how to achieve this with HQL to be honest, however, one way is to convert the date of birth into the day of the year, and then pass your minimum and maximum dates (both converted into the day number of the year) into the HQL. (P.S. by the day of the year i mean eg Feb 1 will be day 32) - i know this is doable within SQL server. – TheITGuy Jun 29 '11 at 03:18