Home » SQL & PL/SQL » SQL & PL/SQL » Combining and comparing date fields
Combining and comparing date fields [message #22589] Fri, 18 October 2002 03:25 Go to next message
Sunil
Messages: 132
Registered: September 1999
Senior Member
I have two dates and two times stored as four separate fields in an Oracle 8 table. The time field is varchar2(4). Suppose the fields are as follows...

A_Date Date,
A_Time Varchar2(4),
B_Date Date,
B_Time Varchar2(4)

I have a 3rd pair of date and time values in my application(VB). I want to fetch records from the above table where this 3rd pair falls between the A and B pairs in the table. How can i do that through an SQL Statement ?
Re: Combining and comparing date fields [message #22594 is a reply to message #22589] Fri, 18 October 2002 04:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
TIME column is of character. you can insert it...
SQL> ed
Wrote file afiedt.buf

  1* select sysdate, to_char(sysdate,'hh24:mi') time from dual
SQL> /

SYSDATE   TIME
--------- -----
18-OCT-02 08:45

Re: Combining and comparing date fields [message #22599 is a reply to message #22589] Fri, 18 October 2002 08:34 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You've made the query a lot uglier though by storing the date and time in separate columns:

select *
  from t
 where to_date(to_char(:vbdate, 'mm/dd/yyyy') || ' ' || :vbtime, 'mm/dd/yyyy hh24:mi') 
   between to_date(to_char(a_date, 'mm/dd/yyyy') || ' ' || a_time, 'mm/dd/yyyy hh24:mi')
       and to_date(to_char(b_date, 'mm/dd/yyyy') || ' ' || b_time, 'mm/dd/yyyy hh24:mi');
Previous Topic: Propogation of exceptions
Next Topic: Column value
Goto Forum:
  


Current Time: Fri May 03 07:49:37 CDT 2024