CODEWITHSUNDEEP
sqlnhibernatehql
BennoDual
BennoDual

Reputation: 6259

Convert SQL to NHibernate HQL

I should convert to the fallowing SQL-Query to NHibernate HQL (NHibernate 3.2). But I don't know, how I have to translate the FLOOR, DATEDIFF and GETDATE.

SELECT Birthday FROM Person
WHERE 1 = (FLOOR(DATEDIFF(dd,Birthday,GETDATE()+10) / 365.25))
      -
      (FLOOR(DATEDIFF(dd,Birthday,GETDATE()-5) / 365.25))

Upvotes: 0

Views: 2554

Answers (2)

Mark Perry
Mark Perry

Reputation: 1735

Something to get started with.

Select Birthdays where Birthday between now-5 days and now+10 days.

    var today = DateTime.Now();
    var results = session.CreateQuery("select p.Birthday from Person 
        where p.Birthday>= :from and p.Birthday<=:to")
    .SetParameter("from", today.AddDays(10))
    .SetParameter("to", today.Subtract(new TimeSpan(5, 0, 0, 0)))
    .List<DateTime>();

Although I think you want to get birthdays regardless of the year.

In order to replicate this query in HQL you are going to have to register the specific SQL functions you need using this technique:

Register Custom SQL Function in NHibernate

Then you should be able to replicate your SQL query in HQL.

This is a good question on the SQL side of things:

SQL Select Upcoming Birthdays

Fresh tactics:

Register the SQL Function for the datediff:

RegisterFunction("datediffdays", 
    new SQLFunctionTemplate(NHibernateUtil.Int32, "datediff(dd,?1, ?2)"));

HQL Query

var result = session.CreateQuery(@"from Person
     where 1 = (floor(datediffdays(Birthday,current_timestamp()+10) / 365.25))
    - (datediffdays(Birthday,current_timestamp()-5) / 365.25))")
.List<Person>();

Upvotes: 2

Phill
Phill

Reputation: 18796

Untested, but would something like: 

var dateStart = DateTime.Now.AddDays(-5).Ticks/365.25D;
var dateEnd = DateTime.Now.AddDays(10).Ticks/365.25D;

session.QueryOver<Person>()
    .WhereRestrictionOn(x => x.Birthday.Ticks/365.25D)
         .IsBetween(dateStart).And(dateEnd)
    .List();

This do the same thing?

Taking a look at the source code, floor is already registered in the dialect, so the following:

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>();

Produces the following SQL:

select person0_.Id       as Id2_,
       person0_.Birthday as Birthday2_
from   [Person] person0_
where  1 = floor(DATEDIFF(dd,
                          person0_.Birthday,
                          GETDATE()
                            + 10)
                   / 365.25)
             - floor(DATEDIFF(dd,
                              person0_.Birthday,
                              GETDATE()
                                - 5)
                       / 365.25)

Upvotes: 0

Related Questions