topic Re: Querying Alfresco on Month of a Date field in Alfresco Archive

Querying Alfresco on Month of a Date field

smsoumitra — Mon, 19 Jan 2015 06:23:37 GMT

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:doc

Re: Querying Alfresco on Month of a Date field

afaust — Mon, 19 Jan 2015 09:31:34 GMT

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

Re: Querying Alfresco on Month of a Date field

mlagneaux — Mon, 19 Jan 2015 17:18:59 GMT

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'