Re: date and time comparision, calculauion

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/02/26
Message-ID: <33145B91.653@iol.ie>#1/1

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