Extract time only from datetime in Oracle

fider
fider used Ask the Experts™
on
I have a table that stores the date/time in datetime format. I need to select the current day's data that fall between a certain time range . I tried to_char() but the comparison against a time range is incorrect as it has been converted. This is what I'm trying to do:

select my_datetime from my_table
where my_datetime between '12:00' and '2:00'

Would appreciate any help.

 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
select my_datetime from my_table
where my_datetime between (trunc(my_datetime) + 12/24) and (trunc(my_datetime) + 14/24)

Author

Commented:
Works great! Thanks for the quick response!
Most Valuable Expert 2011
Top Expert 2012

Commented:
glad I could help
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I could be out of my mind but you can't do an implicate number conversion and change the date format of to_char?
drop table tab1 purge;
create table tab1 (col1 date);

insert into tab1 values(to_date('12:00','HH24:MI'));
insert into tab1 values(to_date('12:01','HH24:MI'));
insert into tab1 values(to_date('13:00','HH24:MI'));
insert into tab1 values(to_date('24:01','HH24:MI'));
commit;

select * from tab1 where to_char(col1,'HH24MI') between 1200 and 1300;

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
drat....  caught typing!!!

Author

Commented:
The query below does execute however the results are incorrect. When i run a similar query where time range is between 9am and 6pm it returns everything after 10:00am (i know i have data for 9:00am). I think it's because the to_char converts it to a string and can't compare it to a time correctly. Just my thought though.

"select * from tab1 where to_char(col1,'HH24MI') between 1200 and 1300;"
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I don't want to confuse the issue if you have a working solution but I can't reproduce the issue you described.

>>to_char converts it to a string
true but in my example Oracle is converting it to a number for me.  You could force it back to a number:
... to_number(to_char(col1,'HH24MI'))...

I've modified my example to add 6AM, 9AM and 11PM and still get what I expect.

Can you add some rows below to show the issue?
drop table tab1 purge;
create table tab1 (col1 date);

insert into tab1 values(to_date('06:00','HH24:MI'));
insert into tab1 values(to_date('09:00','HH24:MI'));
insert into tab1 values(to_date('10:01','HH24:MI'));
insert into tab1 values(to_date('13:00','HH24:MI'));
insert into tab1 values(to_date('23:01','HH24:MI'));
commit;

select * from tab1 where to_char(col1,'HH24MI') between 0900 and 1800;

Open in new window

Author

Commented:
This is what I was doing:

select to_char(col1, 'HH:MM:SS') from test
where to_char(col1, 'HH:MM:SS') between '08:00:00' and '11:00:00'
order by col1 desc

It returns all the records in this range 8:01:xx, 9:01:xx, 10:01:xx and that's it. I have records being inserted every 3 seconds.

It does look like it works if you do a select * rather than the select to_char(col1, 'HH:MM:SS') .

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
You are correct.  As soon as you added the colons you are doing a string/ascii compare.

Notice my select statement, no colon, just numbers.  Oracle will automatically or implicitly convert data types for you if it can.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial