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
Views
Replies
Total Likes
"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
Views
Replies
Total Likes
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.
Views
Replies
Total Likes
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
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.
Were you able to find a solution to this? If so, please give some inputs.
Views
Replies
Total Likes
Views
Likes
Replies
Views
Likes
Replies