check 9 JCR-SQL2 Queries Every AEM Developer Should Know - Blog - 6D
Global SELECT p.* FROM [nt:base] AS p WHERE p.[cq:lastModified] >=
CAST('2008-01-01T00:00:00.000Z' AS DATE) AND p.[cq:lastModified] <=
CAST('2008-12-31T23:59:59.999Z' AS DATE)orSELECT p.* FROM [nt:base] AS
pWHERE p.[some_date] >= CAST('2008-01-01T00:00:00.000Z' AS DATE) AND
p.[some_date] <= CAST('2008-12-31T23:59:59.999Z' AS DATE)