CAST in

[siddharths36808]

Hello ,

I am trying to fetch data from Content node based on some filter criteria and below are my first try

Query  Builder :

path="path of content"

p.hits=selective

1_property = Function

1_property.value= Legal

p.limit=-1

orderby=date

order.sort=desc

SQL 2:

SELECT * FROM [nt:unstructured] as ref WHERE ISDESCENDANTNODE(ref,['path']) AND ref.Function='Legal'  AND ref.date >= CAST('2012-04-01T00:00:00.000+02:00' AS DATE) ORDER BY ref.date DESC

I want the data in sort order based on date Property available in node that is String type.

Actual data return by Query is  :

jobad33180_1 : 4-9-2017

jobad35943_1 : 23-10-2017

jobad36125_1 : 27-10-2017

jobad36127_1 : 27-10-2017

jobad36236_1  :17-11-2017

jobad36743_1 :16-11-2017

Expected Data :

jobad33180_1 : 4-9-2017

jobad35943_1 : 23-10-2017

jobad36125_1 : 27-10-2017

jobad36127_1 : 27-10-2017

jobad36743_1 :16-11-2017

jobad36236_1  :17-11-2017

[smacdonald2008]

To order by date - see this community thread -- Magnolia JCR-SQL2 order by Date - Stack Overflow

[siddharths36808]

Hi Donald,

Date is property available in node its type is "String " we can't modified the property type String to date as it is production data.Please suggest to get all node details in date format asc/desc order.

Sid

[kautuk_sahni]

Do you mean last modified date?

If so then:

Please refer to this wonderful community article:

Link:- Query Builder | CQ5 AEM Tricks of Trade

//Query Builder

In my case,

     http://localhost:4502/bin/querybuilder.json?type=nt:file&nodename=*.jar&orderby=@jcr:conte nt/jcr:lastModified&orderby.sort=asc

     http://localhost:4502/bin/querybuilder.json?type=nt:file&nodename=*.jar&orderby=@jcr:conte nt/jcr:lastModified&orderby.sort=desc

are working fine.

Reference post:- Querybuilder.json: Orderby works only for some properties

If you want something related to date range then see Digest the Query Builder API | CQ5 AEM Tricks of Trade

~kautuk

[siddharths36808]

Hi Kautuksahini,

Date is  property that is available in content and its type is String  and i want to create a query which give me response of all node based on date asc/desc  format .

Sid