Using dates in the "where" clause of an HQL query

I use SQLite and have the following robust class (simplified):

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

which maps 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 launched in a given month.

In SQL, I could use:

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

What I don't like about this request is that it uses the database specific strftime function . "

Thus, the following HQL depends 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 also tried "from Project p, where p.StartDate.Month = 12", but that did not work.

So, using the HQL or API criteria, is it possible to write such a query in the agnostic mode of the database?

+3
source share
3

, , , DateTime - . "" ( )

+4

p, p.StartDate.ToString( "M" );

-1

All Articles