Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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
>
Date values always have a date component, even when you only want the
time. Your 'where' clause asks for rows with time values before 07:00
and after 20:00, which are mutually exclusive conditions. For your last
date expression, try: to_date('07:00','HH:MI') + 1
This will put the comparison into the next day, which appears to be what
you want.
-- +----------------------------------------------------- | Dan Townsend, Supervising Database Architect | EBMUD Enterprise Object Designer | mailto:townsend_at_ebmud.com +-----------------------------------------------------Received on Wed Feb 26 1997 - 00:00:00 CST