Using dates in the "where" clause of HQL query

Question

Hello,

I am using the SQLite database and have the following persistent class (simplified):

public class Project
{
    public virtual int Id { get; set; }
    public virtual DateTime StartDate { get; set; }
}

which is mapped to this table in the database:

CREATE TABLE projects (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    start_date DATETIME
)

Now I need to write a query that will select all the projects that have started in a given month.

In SQL I could use:

SELECT id FROM projects WHERE strftime('%m', start_date) = '12'

What I dislike about this query is that it uses the database specific function "strftime".

So the following HQL is dependent on the underlying database:

// Get all projects that started in December (no matter which year)
var projects = session
    .CreateQuery(
        "from Project p " +
        "where strftime('%m', p.StartDate) = :month")
    .SetParameter("month", "12")
    .List<Project>();

I have also tried "from Project p where p.StartDate.Month = 12" but it didn't work.

So using HQL or criteria API is it possible to write such a query in a database agnostic way?

Answer 1

If you're regularly querying against months, days, years, you shouldn't really be storing your date as a DateTime column - it makes the queries incredibly inefficient. You could easily create a "Month" column and query against that (and your DBA would love you again)

Answer 2

from Project p where p.StartDate.ToString("M");

Answer 3

I would highly recommend you to write a custom function.

http://ayende.com/Blog/archive/2006/10/01/UsingSQLFunctionsInNHibernate.aspx

I don't mean the sql function, I mean the NHibernate function that is registered with RegisterFunction to enhance the NHibernate dialect.

Another probably better example: http://ayende.com/Blog/archive/2007/04/27/Paged-data--Count-with-NHibernate-The-really-easy-way.aspx