JCR query on node property instead of JCR creation/modifiy/publish date?

[carolynr5682886]

Hi Guys-

I am trying to write a query where I can get content from a location in AEM dated from today's day back 24 months, but the date is not based on the publish/modify/creation date. They are asking for the dates to be based on the content called the" publish release date". 

I have the following working off the creation date:

String sqlstmt = "SELECT * FROM [nt:unstructured] AS comp WHERE ISDESCENDANTNODE(comp, '"+pathLocation+"') AND [sling:resourceType] = 'nvenergy/components/content/pressrelease' AND comp.[jcr:created] >= CAST('"+julianEndDateString+"T00:00:00.000Z' AS DATE) AND comp.[jcr:created] < CAST('"+julianCurrentDateString+"T00:00:00.000Z' AS DATE) ORDER BY 1 ASC";

but I really need it to be off the content's "publish release date" found here: 

......

if (currentNode.hasProperty("pressreleasepublishdate")) {
                        Calendar publishedDate = currentNode.getProperty("pressreleasepublishdate").getDate();

....

Can I do a join or used something to use the node's property for the release date instead of the [jcr:created] as my range? Or do I have to execute the query hold it in a temp list and resort based on the node's property for the release date? 

[smacdonald2008]

I would test for that prop and then if its there - query for the range you want. 

[carolynr5682886]

So like this? 

String sqlstmt = "SELECT * from [nt:base] AS comp WHERE ISDESCENDANTNODE('"+pathLocation+"') AND (comp.[pressreleasepublishdate] <> '' AND comp.[pressreleasepublishdate] > CAST('"+julianCurrentDateString+"T00:00:00.000Z' AS DATE)) ORDER BY comp.[pressreleasepublishdate] ASC";

[carolynr5682886]

I don't think that I can get the values back for this property when running the query with the above request for the property value. Through the logger I am not seeing the value being replaced by the property name: 

16.05.2017 15:23:00.144 *INFO* [0:0:0:0:0:0:0:1 [1494973379652] GET /content/NVEnergy/en/about-nvenergy/news/news-releases.html HTTP/1.1] com.nvenergy.nvenergy.core.models.PressReleaseList SQL Statement: SELECT * from [nt:base] AS comp WHERE ISDESCENDANTNODE('/content/NVEnergy/en/about-nvenergy/news/') AND (comp.[pressreleasepublishdate] <> '' AND comp.[pressreleasepublishdate] > CAST('2017-05-16T00:00:00.000Z' AS DATE)) ORDER BY comp.[pressreleasepublishdate] ASC

When I run though the logic of looking at the child nodes property for ""pressreleasepublishdate"  from the result set I can get this:  

16.05.2017 17:41:23.672 *INFO* [0:0:0:0:0:0:0:1 [1494981683410] GET /content/NVEnergy/en/about-nvenergy/news/news-releases.html HTTP/1.1] com.nvenergy.nvenergy.core.models.PressReleaseList press release publishdate is : Nov 1, 2016