Querying Alfresco on Month of a Date field

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

smsoumitra

Is there any way to query on the basis of month and year of any date field in Alfresco. I'm using aspect where in one of the custom properties is of date type. But I need to query on files based on the month and year of the field.

This is what I'm doing.
 

 String Query = "select d.*,o.* from cmis:document as d join sc:mine as o on d.cmis:objectid = o.cmis:objectid";
   //"where MONTH(o.sc.createdDateTime) = JAN"
‍‍‍

And then something like:

ItemIterable<QueryResult> results = session.query(Query, false);
for(QueryRsult qr : results){
   Calendar cal = (Calendar) qr.getPropertyValueByQueryName("o.sc:createdDateTime");
   Integer month = cal.get(Clendar.MONTH);
   Integer year = cal.get(Clendar.YEAR);
//Select the required month/year and then proceed
}
‍‍‍‍‍‍‍‍‍

But in this approach every time we have to loop through all the results. It will really helpful if I can query on the month of sc:createdDateTime

afaust

Hello,

unfortunately, the CMIS specification as far as I know does not provide a facility to query for natural year, month or date in isolation (without a complete timestamp) on date fields. But you may be able to use Alfresco full text support to do a simple contains check in your CMIS query, e.g. use CONTAINS('cmis:creationDate:\'2015-01-??\'') to select a specific month and retrieve only the relevant subset of documents (for each year - unfortunately it seems you can't use ???? wildcards for the year to retrieve all the January documents regardless of year).

Regards
Axel