Date Comparison in Oracle


I have a date field in Oracle. If I use TO_CHAR(My_date, 'YYYY-MM-DD HH24:MI:SS'), it gives me output like '2014-09-23 04:48:00'.

How to select the records which has My_date>='2014-09-23 10:00:00' and My_date<='2014-09-23 16:00:00' ?

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

slightwv (䄆 Netminder)Connect With a Mentor Commented:
Sorry.  Missed an 'H':
select * from table_name
 My_date>=to_date('2014-09-23 10:00:00','YYYY-MM-DD HH24:MI:SS') and My_date<=to_date('2014-09-23 16:00:00','YYYY-MM-DD HH24:MI:SS')
slightwv (䄆 Netminder) Commented:
use to_date to convert strings to dates.

select * from table_name
 My_date>=to_date('2014-09-23 10:00:00','YYYY-MM-DD H24:MI:SS') and My_date<=to_date('2014-09-23 16:00:00','YYYY-MM-DD H24:MI:SS')
Haris DjulicConnect With a Mentor Commented:

where my_date >=to_Date('2014-09-23 10:00:00','YYYY-MM-DD HH24:MI:SS') and my_date<=('2014-09-23 16:00:00','YYYY-MM-DD HH24:MI:SS')

Open in new window

Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

mrongAuthor Commented:
Try your suggestions but got the following error.

where my_date>=to_date('2014-02-13 17:00:00','YYYY-MM-DD H24:MI:SS')  
ERROR at line 2:
ORA-01821: date format not recognized
Gerwin Jansen, EE MVETopic Advisor Commented:
and a 'where' :)

select * from table_name
where My_date>=to_date('2014-09-23 10:00:00','YYYY-MM-DD HH24:MI:SS') and My_date<=to_date('2014-09-23 16:00:00','YYYY-MM-DD HH24:MI:SS');

(using between)

select * from table_name
where My_date between to_date('2014-09-23 10:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2014-09-23 16:00:00','YYYY-MM-DD HH24:MI:SS');
Mark GeerlingsDatabase AdministratorCommented:
Remember to apply these "to_date" conversions to bind variables in your queries, and not to the database column values, or you cause a big performance penalty.
slightwv (䄆 Netminder) Commented:
>>or you cause a big performance penalty.

To clarify what this piece means:
If a column has an index on it, wrapping that column in a function will not allow the index to be used.

Say you have an index on my_date.

This may use the index:
select count(*) from my_table where my_date >= to_date('01/01/2001','MM/DD/YYYY')
and my_date < to_date('01/02/2001','MM/DD/YYYY');

This will never use the index:
select count(*) from my_table where to_char(my_date,'MM/DD/YYYY') = '01/01/2001';

You 'can' create a function-based index with to_char(my_date,'MM/DD/YYYY') but most of the time this is unnecessary and a topic for another question.
All Courses

From novice to tech pro — start learning today.