Combining and comparing date fields [message #22589] |
Fri, 18 October 2002 03:25 |
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 #22599 is a reply to message #22589] |
Fri, 18 October 2002 08:34 |
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');
|
|
|