[kronolith] configuring kronolith for use w/ sql

[Liam Hoekenga]

> SELECT DISTINCT e.event_id FROM
> kronolith_events WHERE calendar_id = 'liamr'
> AND event_start >= {ts '2001-09-01 00:00:00.000'}
> AND (event_end <= {ts '2001-09-30 23:59:59.999'}
> OR (event_recurEnddate <= {ts '2001-09-30 23:59:59.999'}
> AND event_recurType != 0))

I couldn't execute the sql from sqlplus either:

	ORA-00911: invalid character

Here's what our DBA had
to say about it...

---------- Forwarded message ----------
Date: Tue, 18 Sep 2001 15:53:40 -0400 (EDT)
From: Kasthuri
To: Liam Hoekenga <liamr@umich.edu>
Subject: Re: [kronolith] configuring kronolith for use w/ sql (fwd)

Liam, THe following sql should work provided event_start, event_end,
event_recurEnddate are all defined as date datatype. If those are varchar2
then you have to change the to_date function to to_char function. Also
there is one other problem. I don't know how you can store & retrieve from
oracle a column with fraction of a second like how you have it in the
query you sent me. I have taken the millisecond info from the following
sql. If you need to track millisecond & microsecond, you need to define an
another column to store just millisecond and concatenate the two columns
to get the full date& time. I'm not very sure about this. I will search
around and find out about this milli/micro second information.

SELECT DISTINCT e.event_id FROM
kronolith_events WHERE calendar_id = 'liamr'
AND event_start >= to_date('2001-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),
AND (event_end <= to_date('2001-09-30 23:59:59','YYYY-MM-DD HH24:MI:SS'),
OR (event_recurEnddate <= to_date('2001-09-30 23:59:59','YYYY-MM-DD
HH24:MI:SS')
AND event_recurType != 0))

-Kasthuri

On Tue, 18 Sep 2001, Liam Hoekenga wrote:

> Hey Kasthuri -
>
> Can you help me come up with a properly formatted oracle version of this
> sql query?
>
> SELECT DISTINCT e.event_id FROM
> kronolith_events WHERE calendar_id = 'liamr'
> AND event_start >= {ts '2001-09-01 00:00:00.000'}
> AND (event_end <= {ts '2001-09-30 23:59:59.999'}
> OR (event_recurEnddate <= {ts '2001-09-30 23:59:59.999'}
> AND event_recurType != 0))
>
> thanks!
> Liam