Re: date and time comparision, calculauion

John Remus wrote:
>
> Hi everybody!
>
> I have a little problem with the date datatype handling.
>
> I like to select a date(time) type column and restrict it for example
> between 20:00 and 7:00.
> I tried like this:
>
> select to_char(time,'HH:MI')
> from time_table
> where to_date(to_char(time,'HH:MI')) > to_date('20:00','HH:MI') and
> to_date(to_char(time,'HH:MI')) < to_date('07:00','HH:MI')
> ;
>
> In the same way I would like to know how I can calculate times and
> dates with each other.
>
> Thank's in advance
>
> John Remus
> John_Steven.Remus_at_akh-wien.ac.at
> General Hospital Vienna
> Austria
> John Remus
> John_Steven.Remus_at_akh-wien.ac.at
> General Hospital Vienna
> Austria

Try this:

select to_char(time,'HH:MI')
  from time_table
 where to_number(to_char(time,'HH24MI')) between (2000, 2400)

    or to_number(to_char(time,'HH24MI')) between (0, 700)

I didn't test it, so it may need tweaking. Also if this a big table, the query will be very slow.

-- 
Thomas Griffin
Project Leader
QUALITECH Systems, Inc.
tgriffin_at_qualitech.com