Re: date and time comparision, calculauion

I should have included in my reply that the most straightforward way of doing date/time comparisons is also the most efficient (since it can use an index on the column):

select ...
from temp
where edate between to_date('02jan1997133000','ddmmyyyyhh24miss')

                 and to_date('02jan1997143100','ddmmyyyyhh24miss')

Chrysalis wrote:
>
> John Remus wrote:
> >
> > I like to select a date(time) type column and restrict it for example
> > between 20:00 and 7:00.
>
> It'fairly straightforward. Just remember to compare like datatypes.
> SQL> col date_time format a20;
> SQL> col secs format 99990;
> SQL>
> SQL>
> SQL> select id
> 2 ,to_char(edate,'dd-Mon-yyyy hh24:mi:ss') date_time
> 3 ,to_number(to_char(edate,'sssss')) secs
> 4 from temp
> 5 where edate between '2-jan-97' and '4-jan-97' -- time truncated
> 6 order by edate
> 7 /
>
> ID DATE_TIME
> SECS
> -------- --------------------
> ------
> 7698 02-Jan-1997 13:30:27
> 48627
> 7844 02-Jan-1997 14:31:46
> 52306
> 7566 03-Jan-1997 01:50:19
> 6619
> 7934 03-Jan-1997 09:03:28
> 32608
> 7654 04-Jan-1997 00:00:00
> 0
> SQL>
> SQL>
> SQL> Rem
> SQL> Rem Partial date comaprison (ignore seconds)
> SQL> Rem
> SQL> select id,to_char(edate,'dd-Mon-yyyy hh24:mi:ss') edate
> 2 from temp
> 3 where trunc(edate) = '2-jan-97' -- Don't forget this part!
> 4 and to_char(edate,'hh24:mi') between '13:30'
> 5 and '14:31'
> 6 order by edate
> 7 /
>
> ID
> DATE_TIME
> --------
> --------------------
> 7698 02-Jan-1997
> 13:30:27
> 7844 02-Jan-1997
> 14:31:46
> SQL>
> SQL>
> SQL> Rem
> SQL> Rem Or you can do a full numeric comparison
> SQL> Rem
> SQL> select id,to_char(edate,'dd-Mon-yyyy hh24:mi:ss') edate
> 2 from temp
> 3 where trunc(edate) = '2-jan-97'
> 4 and to_number(to_char(edate,'sssss')) between 60*(60*13+30)
> 5 and 60*(60*14+31)
> 6 order by edate
> 7 /
>
> ID
> DATE_TIME
> --------
> --------------------
> 7698 02-Jan-1997
> 13:30:27
> SQL>
> SQL>
> SQL> set echo off;
>
> Hope this helps.
>
> Chrysalis.
  Received on Wed Feb 26 1997 - 00:00:00 CST