Expand my Community achievements bar.

Enhance your AEM Assets & Boost Your Development: [AEM Gems | June 19, 2024] Improving the Developer Experience with New APIs and Events

Compare date field in jcr query

Avatar

Level 6

Hi All,

I need to fetch documents based on date fields (jcr:created & jcr:lastModified) , below is my query

SELECT * FROM [dam:Asset] WHERE (ISDESCENDANTNODE([/content/dam/secure-internal]) OR ISDESCENDANTNODE ([/content/dam/common])) and ([jcr:created] >= [jcr:content/jcr:lastModified])

I am getting below error

java.text.ParseException: Query: SELECT * FROM [dam:Asset] WHERE (ISDESCENDANTNODE([/content/dam/secure-internal]) OR ISDESCENDANTNODE ([/content/dam/common])) and ([jcr:created] >= [jcr:content/jcr:lastModified](*)); expected: static operand

Please help.

Thanks,

Pradeep

5 Replies

Avatar

Level 6
        Hi, I need to compare 2 date fields within the document, there is no outside date input in my query. Hence that blog is not useful to me.

Avatar

Level 10

"I need to compare 2 date fields within the document"

It does not matter what the AEM resource is. You specify the resource in the Query. AEM Users is just an example of an AEM resource. The date part of the Query is the important part. 

 String sqlStatement2 = "select * from [rep:User] where isdescendantnode('/home/users/mypath') and [jcr:created] > cast('"+ year +"-"+monthStr+"-"+dayStr+"T00:00:00.000+02:00' as date) and [jcr:created] < cast('"+ year2 +"-"+monthStr2+"-"+dayStr2+"T23:59:59.000+02:00' as date)";

So instead of searching for users (this was just an example) you would search for documents at a given path.  

"there is no outside date input in my query"

Replace use of variables in this example with hard-coded date values. But this is the syntax you would use. 

This example was based on this thread:

http://stackoverflow.com/questions/15117075/jcr-sql2-query-comparing-dates

Avatar

Level 10

As discussed in that Stackoverflow thread: 

The best way to use dates in a query is to use CAST to force the conversion of a string representation of a date/time into a DATE value used by JCR.

In your example: 

SELECT * FROM [dam:Asset] WHERE (ISDESCENDANTNODE([/content/dam/secure-internal]) OR ISDESCENDANTNODE ([/content/dam/common])) and ([jcr:created] >= [jcr:content/jcr:lastModified])

You have not done that. 

Avatar

Level 10

Hi,

Scott is right!!

Yes, you need to "CAST to force the conversion of a string representation of a date/time into a Date value used by JCR" as mentioned in Stack overflow thread.

~ Ratna

Avatar

Level 6

Hi All,

I tried a lot even after CAST it is not working, see below query 

SELECT * FROM [dam:Asset] WHERE (ISDESCENDANTNODE([/content/dam/secure-internal]) OR ISDESCENDANTNODE ([/content/dam/common])) and (CAST([jcr:created] AS DATE) > CAST([jcr:content/jcr:lastModified] AS DATE))

Tried putting both the dates in single quote too, nothing worked. 

Please advise.