[Resolved] Query date

nebulom · **Joined:** Tue May 23, 2006 12:53 am **Posts:** 34

How do I query given date's month and date's year?

Something like.... Get list from table where date's month is 6 and date's year is 2006? I'm kinda new to NHibernate.... so any help? Thanks!

abombss · **Posted:** Mon Jun 26, 2006 12:49 pm

Year, Month, and Day functions are not supported yet. You will have to use a range query for the start of the month and the end of the month.

http://jira.nhibernate.org/browse/NH-568

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

You should be able to use the DATEPART function from SQL server or any equivalent from your RDBMS.

For example, if using SQL Server you can execute the following to return all jobs created on a Sunday:

Code:

SELECT j FROM Job j WHERE DATEPART(dw, j.Created) = 1

Hope that helps,

Symon.

nebulom · **Joined:** Tue May 23, 2006 12:53 am **Posts:** 34

I mean something like

Code:

         Read r = new Read("from Article where Date.Month = 6 and Date.Year = 2006");
         r.Execute();
         IList list = r.List;
         foreach (Article a in list) 
            Console.WriteLine(a.Date.ToString("D"));

Read btw, is an Action class. It returns nothing btw. Any suggestion(s)?

abombss · **Posted:** Tue Jun 27, 2006 2:32 am

The only way to do something like that would be if date were a component and the fields for year, month, day were mapped to actual columns in the database.

All HQL query does is translate properties of your objects into real sql against your database. If a property is not mapped to the database HQL has no idea how to generate the proper the SQL.

Dates are a specific case, but think about this...

Code:

public class FooBu
{
    public Int32 Id;
    public String Foo;
    public String Bar;
    public String FooBar { get { return Foo + Bar; } }
}

Code:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="FooBu" table="dbo.t_FooBu" >
      <id name="Id">
         <generator class="native">
         </generator>
      </id>
      <property name="Foo" />
      <property name="Bar" />
   </class>
</hibernate-mapping>

If I ran the following query how would HQL translate this to SQL.

Code:

"from FooBu where FooBar = "HelloWorld!"

NHibernate has no idea how FooBar is derived, likewise in your situation, NHibernate has no idea what Month is. As mentioned above, a Date is a specific case and a feature could probably added for DateTime fields to support properties, but in general this is the reaosn for your problem.

Your best bet is using merge_s.rottem's advice and use the datepart function with a parameter.

nebulom · **Joined:** Tue May 23, 2006 12:53 am **Posts:** 34

Thanks. I'm understanding a bit of Hibernate. So, any pointers on how do I make my class to have this DATEPART thing? Or am I in the right track?

I added 2 public properties and map it

Code:

public int Month 
      {
         get { return month; }
         set { month = value; }
      }

      public int Year 
      {
         get { return year; }
         set { year = value; }
      }

Code:

<property name="Month" formula="datepart(mm, Date)" insert="false" update="false"/>
      <property name="Year" formula="datepart(yyyy, Date)" insert="false" update="false"/>   

Now, I need help on the syntax of my HQL. How do I query given a Month and the Year? Sori for my noob questions but I try learning here. I really do. Again, thanks!

[edit]
Btw, I have

Code:

Find f = new Find("from Article where Month = 6 and Year = 2006");
         f.Execute();
         IList list = f.List;
         foreach (Article a in list) 
            Console.WriteLine(a.Keyword);

But gives me

Code:

TestCase 'NBulletin.Commands.Test.CommandTest.TestFindArticles'
failed: NHibernate.ADOException : Could not execute query
  ----> System.Data.SqlClient.SqlException : Invalid column name 'Month'.
   at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters)
   at NHibernate.Impl.SessionImpl.Find(String query)
   D:\ian\projects\csharp\nbulletin\src\NBulletin.Core\Commands\Commands.cs(138,0): at NBulletin.Commands.Find.Execute()
   d:\ian\projects\csharp\nbulletin\src\nbulletin.core\commands\test\commandtest.cs(30,0): at NBulletin.Commands.Test.CommandTest.TestFindArticles()
   --ADOException
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
   at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
   at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
   at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
   at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
   at NHibernate.Hql.QueryTranslator.List(ISessionImplementor session, QueryParameters queryParameters)
   at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters)

TestFixture failed: 

]

benhyrman · **Joined:** Wed Jun 21, 2006 3:13 pm **Posts:** 110

I find it extremely helpful to have show_sql set in your NHibernate config

if you're using the nhibernate config handler

Code:

If you're using the name-value handler, then I think it's

Code:

This will save your prepared SQL statement for you. If one's generated, then you know nhibernate at least got through the mapping and then you can debug the actual query in Query Analyzer. Month might need to be escaped as a reserved word, but getting your hands on the sql output would help.

nebulom · **Joined:** Tue May 23, 2006 12:53 am **Posts:** 34

Sorry for the very late reply but it gives me error

Code:

NHibernate.ADOException : Could not execute query

and it gives the sql query

Code:

NHibernate: select article0_.Id as Id, article0_.Text as Text, article0_.Image as Image, article0_.Keyword as Keyword, article0_.Caption as Caption, article0_.SectionId as SectionId, article0_.IsTopStory as IsTopStory, article0_.Date as Date, article0_.IsInactive as IsInactive, datepart(article0_.yyyy, article0_.Date) as f1_, datepart(article0_.mm, article0_.Date) as f0_ from Articles article0_ where (Month=@p0)and(Year=@p1)

So, it's clear that Month is not a column of the table and also the year. But how do I datepart() the Date column?

:(

nebulom · **Joined:** Tue May 23, 2006 12:53 am **Posts:** 34

I am so dumb. Ok, I found it with datepart(mm, Date) = ? and datepart(yyyy, Date) = ?

But is datepart a standard SQL function? Anyway, resolved for now. Thanks a bunch!

tahirakram · **Posted:** Tue Feb 06, 2007 8:28 am

I was also messing with this issue but find out solution in HQL,

Code:

select createDate from UserAccount ua where month(ua.createDate) = 1 and year(ua.createDate) = 2007

hope it helps