Compare date field in jcr query

[pradeepdubey82]

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

[pradeepdubey82]

        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.

[smacdonald2008]

"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

[smacdonald2008]

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. 

[Ratna_Kumar]

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

[pradeepdubey82]

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.