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:
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?
source
share