Hi
here some code which is working for me:
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String dateFirst = sdf.format(cFirstDay.getTime());
String dateLast = sdf.format(cLastDay.getTime());
final String statement = "SELECT p.* FROM [nt:base] AS p WHERE p.[startDate] >
CAST('" + dateFirst + "T00:00:00.000Z' AS DATE) "
+ "AND p.[startDate] <
CAST('" + dateLast + "T00:00:00.000Z' AS DATE)"
+ "
order by p.[startDate] asc";
final String language = "JCR-SQL2”;
result = QueryUtil.search(“website", statement, language);
HTH Tom
On 26 Nov 2014, at 13:22, Sambit Pani (via Magnolia Forums)
<[email protected]> wrote:
> In my data repo i have a String property which have store date as String in
> 'dd/MM/YYYY hh:mm:ss' format. I want an sql query which will give me all the
> node for which that property value as date fall in between two date.
>
> I am trying with below query but its not working.
> select * from nt:base where jcr:path like '/MyData/%' and CAST(r_date as
> DATE) between 2014-11-12 and 2014-11-18
>
> But its showing error as ' Encounter "r_date" at line1: Was expecting ")"...
>
> Please anyone have any idea for this kind of query please share.
>
> --
> Context is everything:
> http://forum.magnolia-cms.com/forum/thread.html?threadId=b34cbea9-2d14-4263-8e6a-ad518b8c79e4
>
>
> ----------------------------------------------------------------
> For list details, see http://www.magnolia-cms.com/community/mailing-lists.html
> Alternatively, use our forums: http://forum.magnolia-cms.com/
> To unsubscribe, E-mail to: <[email protected]>
> ----------------------------------------------------------------
>
----------------------------------------------------------------
For list details, see http://www.magnolia-cms.com/community/mailing-lists.html
Alternatively, use our forums: http://forum.magnolia-cms.com/
To unsubscribe, E-mail to: <[email protected]>
----------------------------------------------------------------
