Using dates in the "where" clause of HQL query

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?

From stackoverflow

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

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

  Darin Dimitrov : David, I have tried what you suggested but I got the same exception as if I used p.StartDate.Month = 12. NHibernate.QueryException: dereferenced: StartDate

• 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