CAST in

Avatar

Avatar

siddharths36808

Avatar

siddharths36808

siddharths36808

27-11-2017

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

Replies

Avatar

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Reply

2.3K

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Reply

2.3K
smacdonald2008

27-11-2017

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

Avatar

Avatar

kautuk_sahni

Community Manager

Total Posts

5.6K

Likes

1.0K

Correct Reply

1.1K

Avatar

kautuk_sahni

Community Manager

Total Posts

5.6K

Likes

1.0K

Correct Reply

1.1K
kautuk_sahni
Community Manager

28-11-2017

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

Avatar

Avatar

siddharths36808

Avatar

siddharths36808

siddharths36808

28-11-2017

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 .

1359866_pastedImage_0.png

Sid

Avatar

Avatar

siddharths36808

Avatar

siddharths36808

siddharths36808

28-11-2017

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.

1359867_pastedImage_0.png

Sid