Date and Time matching in Where clause in Oracle

Date and Time matching in Where clause in Oracle:

It is trick when you want to match date or  date and time in where clause of you oracle sql query.
some time the original field may will be in date, time or some other field. and when you will put date type data with different format in where clause of you sql query it will mismatch and will not provide you the result you want.

You can avoid this kind of problem by using the to_date(date, format) in you where clause and the original field name as usual.

Below is an example:

select *
from table_name
where assoc_id = '6492349324'
and begin_date = TO_DATE('01-APR-2012', 'DD-MON-YYYY')  --what ever will be the data type of begin_date
and end_date = TO_DATE('01-JUL-2012', 'DD-MON-YYYY')    --the query will only match the date out of it


In the above query any date type data can be matched.