Querying Alfresco on Month of a Date field

[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

[mlagneaux]

Hi,

I think that asing a comparison predicate will make the point. Here is an example if you want to get documents with creationDate in January 2015 :
SELECT * FROM cmis:document WHERE cmis:creationDate >= TIMESTAMP '2015-01-01T00:00:00.000+00:00' AND cmis:creationDate < TIMESTAMP '2015-02-01T00:00:00.000+00:00'