Compare date field in jcr query

"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

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. 

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.