Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

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' ?

2 Solutions
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 DjulicCommented:

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

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

slightwv (䄆 Netminder) 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')
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.

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now