Time/Date Ranges in Oracle 8.x

Perhaps one of you out there can help...
I am trying to create a report in Oracle based on time and date ranges. The date range works perfectly but I am running into issues with the time range. I am thinking that the query does not know to associate the time with the date.

The table has two separate fields for time and date. Neither of which were designed to use the a database date/time format, so this report has been tedious to write.

I am programming the report application in ColdFusion MX, fyi.

Here is my query:

SELECT *
FROM table_1
WHERE to_date(date1,'YYYYMMDD') between to_date('20031201','YYYYMMDD') and to_date('20031202','YYYYMMDD')
AND to_date(lpad(time_field,6,'0'),'HH24MISS') between to_date('000000','HH24MISS') and to_date('070000','HH24MISS')
ORDER BY internet_creation_date desc, internet_creation_time desc

I was thinking that my solution might be found in subqueries under the WHERE clause.

Any help would be appreciated.

breklin

I think this should work


it just concatenates the date and time togther to get a date field, then the comparison is easier.

don't know why i didn't think of that. so simple. anyhow thanks. it's solved my issue.