Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle 8i - select between to date ranges, but exculde time ranges

589205Oct 19 2007 — edited Oct 20 2007

Hi, I'm using Oracle 8i

I'm trying to select a set of records that are between a certain date time (say between October 15 at 6pm and October 17 at 6am) and then want to include only those records that fall between 6am and 6pm within the range.

I've tried extract and trunc function and can't seem to get it to work.

If I do a trunc to try to pull out only hour, it returns all of the date information as well:

SQL> select trunc(to_date('14-10-2007 02:43:46','DD-MM-YYYY HH24:MI:SS'),'HH24') from dual;

TRUNC(TO_DATE('14-10
--------------------
14-OCT-2007 02:00:00

SQL>

Any Ideas? Here is some sample data:

select * from
(
        select to_date('14-10-2007 02:43:46','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('14-10-2007 03:02:50','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('14-10-2007 15:13:16','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('14-10-2007 15:16:04','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('14-10-2007 15:18:26','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('14-10-2007 15:20:25','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('14-10-2007 15:22:35','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('14-10-2007 15:23:59','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('14-10-2007 15:26:30','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('14-10-2007 15:33:30','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('14-10-2007 15:54:36','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('14-10-2007 15:56:11','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('14-10-2007 18:56:52','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('15-10-2007 09:12:38','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('15-10-2007 10:23:42','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('15-10-2007 11:17:32','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('15-10-2007 11:46:12','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('15-10-2007 12:36:22','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('15-10-2007 23:23:17','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('16-10-2007 14:43:06','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('16-10-2007 14:44:37','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('16-10-2007 14:48:17','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('16-10-2007 14:49:36','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('16-10-2007 15:07:05','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('16-10-2007 15:08:24','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('17-10-2007 08:55:33','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('17-10-2007 09:58:19','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('17-10-2007 15:07:16','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('17-10-2007 15:19:35','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('17-10-2007 15:58:32','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('17-10-2007 19:56:51','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('17-10-2007 21:22:49','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('17-10-2007 22:16:52','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('17-10-2007 22:45:51','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('18-10-2007 07:52:10','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('18-10-2007 07:54:15','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('18-10-2007 08:03:57','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('18-10-2007 08:31:27','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('18-10-2007 09:16:14','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('18-10-2007 11:10:55','DD-MM-YYYY HH24:MI:SS') from dual union all
        select to_date('18-10-2007 11:21:57','DD-MM-YYYY HH24:MI:SS') from dual
) DataSet

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 17 2007
Added on Oct 19 2007
11 comments
741 views