Hi Fernando, I've tried to do this same thing myself, and have posted similar questions to the list a few times, but have never figured out a syntax that would actually work. My impression is that the data in the repository is structured in such a way that standard JCR queries have a tough time getting to that data (at least for ORDER BY clauses, which are what I was after).
I ended up diving into the API for the work I was doing. I just documented it on the wiki here: <http://www.magnolia.info/wiki/Wiki.jsp?page=EventCalendar>. Among the various things I tackled were filtering based on date. It's not as clean as using <cms:query> would be, but isn't too bad. Hope that helps a bit. Sean On 9/25/06 5:38 AM, "Fernando Igual" <[email protected]> wrote: > Hello everybody. > > I'm trying to make a query to website repository using SQL language: > > SELECT * > FROM nt:base > WHERE jcr:path LIKE '/a/b/%/%' > and sector='a' > and > ( > (actionStartDate>='2006-01-01T00:00:00+0100' AND > actionStartDate<'2007-01-01T00:00:00+0100') > ) > > actionStartDate is a paragraph field (Date type). A page contains a > paragraph with actionStartDate = 2006-02-19 (the result is > '2006-02-18T23:00:00.000Z' if I print like a string, I'm in GMT+1 time > zone). > > sector is a field (string) of the same paragrapth. > > If I try to execute the sql: > > SELECT * > FROM nt:base > WHERE jcr:path LIKE '/a/b/%/%' > and sector='a' > > the result collection contains a Content node. This is the page that > contains this paragraph. > > How can I filter SQL over repository with date fileds? > > Thanks in advance. > > > > > > > ---------------------------------------------------------------- > for list details see > http://www.magnolia.info/en/magnolia/developer.html > ---------------------------------------------------------------- ---------------------------------------------------------------- for list details see http://www.magnolia.info/en/magnolia/developer.html ----------------------------------------------------------------
