Convert SQL to NHibernate HQL

Question

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

@Phill - Definition of FLOOR: Returns the largest integer less than or equal to the specified numeric expression. FLOOR(DATEDIFF(dd,Birthday,GETDATE()+10) / 365.25) gives me the age in 10 days - FLOOR(DATEDIFF(dd,Birthday,GETDATE()-5) / 365.25) gives me the age it was 5 days in the past. If the difference of this is 1 the birthday is between 5 days in the past from now and 10 days in the future. — BennoDual, Jun 29 '11 at 08:45

score 2 · Answer 1 · edited May 23 '17 at 11:55

2

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

edited May 23 '17 at 11:55

Community

1
1

answered Jun 29 '11 at 08:11

Mark Perry

1,705
10
12

Yes, my select gives me the birtdays regardless of the year. I don't know now, what I have to do to use the MS SQL Server Keywords in HQL. – BennoDual Jun 29 '11 at 08:41
@t.kehl You can create a custom dialect and register the sql functions you need using this technique: http://nhforge.org/blogs/nhibernate/archive/2009/03/13/registering-freetext-or-contains-functions-into-a-nhibernate-dialect.aspx – Mark Perry Jun 29 '11 at 08:44
@t.kehl Although I must admit I have never tried nesting the custom SQL functions as you will need to do. – Mark Perry Jun 29 '11 at 09:01
http://www.symbolsource.org/Public/Metadata/Default/Project/NHibernate/3.0.0.Alpha1-4950/Release/All/NHibernate/NHibernate/Dialect/MsSql2000Dialect.cs - The source shows floor is already registered. – Phill Jun 29 '11 at 09:25

Phill · Accepted Answer · 2011-06-29T09:33:13.743

0

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)

edited Jun 29 '11 at 09:33

answered Jun 29 '11 at 09:01

Phill

18,398
7
62
102

Ticks in C# work from 1/1/0001 but in SQL Server they work from different dates depending on the column type, DateTime (SQL 2000) vs DateTime2 (SQL 2008). – Mark Perry Jun 29 '11 at 09:09
@Mark - good point, didn't think of that. It's clearly too late in the day to be thinking. – Phill Jun 29 '11 at 09:12
this gives me the exception with message: Could not determine member from (Convert(x.Birthday.Value.Ticks) / 365,25). Birthday is on my side a Nullable-DateTime. – BennoDual Jun 29 '11 at 09:38
@t.kehl - I crossed that solution out because of Marks comment about the DateTime differences betweeen .NET and SQL Server. – Phill Jun 29 '11 at 09:54

Convert SQL to NHibernate HQL

2 Answers2