You are viewing a plain text version of this content. The canonical link for it is here.

Posted to users@jackrabbit.apache.org by Lei Zhou <Le...@pointalliance.com> on 2006/12/01 22:55:46 UTC

SQL query with datetime values

Hi, 

Could anyone advise what is the correct format to write the following SQL 
queries that includes datetime comparisons?

select * from my:nodeType 
where my:dateProp = xs:dateTime('2006-11-22T00:00:00.000-05:00')


select * from my:nodeType 
where 
   my:dateProp1 > xs:dateTime('2006-11-22T00:00:00.000-05:00') 
and 
   my:dateProp2 < xs:dateTime('2006-11-25T00:00:00.000-05:00')


The XPath query works fine. Just wonder if there is any equivalent in Sql.

Thanks,
Lei

Re: SQL query with datetime values

Posted by Marcel Reutegger <ma...@gmx.net>.

Thomas Mueller wrote:
>> Jackrabbit uses the SQL 92 standard:
>>
>> SELECT * FROM my:nodeType
>>      WHERE my:dateProp = TIMESTAMP "2006-11-22T00:00:00.000-05:00"
> 
> Actually, the 'T' between date and time is ISO 8601, and not SQL 92.
> The " around the timestamp would be ' in standard SQL.

you are right.

and there was actually a bug in the parser that prevented the use of a space 
instead of the 'T', even though the initial intention was that one could use 
either. See: http://issues.apache.org/jira/browse/JCR-660

regards
  marcel

Re: SQL query with datetime values

Posted by Thomas Mueller <th...@gmail.com>.

> Jackrabbit uses the SQL 92 standard:
>
> SELECT * FROM my:nodeType
>      WHERE my:dateProp = TIMESTAMP "2006-11-22T00:00:00.000-05:00"

Actually, the 'T' between date and time is ISO 8601, and not SQL 92.
The " around the timestamp would be ' in standard SQL.

Sorry for beeing a SQL grammar nazi...

Thomas

Re: SQL query with datetime values

Posted by Marcel Reutegger <ma...@gmx.net>.

Lei Zhou wrote:
> Could anyone advise what is the correct format to write the following SQL 
> queries that includes datetime comparisons?
> 
> select * from my:nodeType 
> where my:dateProp = xs:dateTime('2006-11-22T00:00:00.000-05:00')

xs:dateTime() is a XPath specific function and cannot be used in SQL. Jackrabbit 
uses the SQL 92 standard:

SELECT * FROM my:nodeType
     WHERE my:dateProp = TIMESTAMP "2006-11-22T00:00:00.000-05:00"

regards
  marcel