Q:

How to retrieve the records based on a date from oracle database

I have a table with date column in it.  I need to fetch the records from it based on 
the given date. 
Currently when i used the query:
select * from workingemployee_data where created_date like '20-Jan-2012'

I am getting those records which have created_date on 20-Jan-2012
But i want to get the records those were created 10 days earlier to a given 
date (i.e) 20-Jan-2012. 
Please suggest me on this. 

A:

This gives all records between today and 10 days ago:
SELECT *
FROM workingemployee
WHERE created_date BETWEEN sysdate - INTERVAL '10' DAY
                       AND sysdate

This gives all records entered exactly 10 days ago:
SELECT *
FROM workingemployee
WHERE created_date = sysdate - INTERVAL '10' DAY

Replace sysdate with exact date if you want. 

A:

Why do you use like and not = ? 
Assuming that created_date is of type DATE, it's bad practice to rely on implicit conversion according to NLS_DATE_FORMAT (this is what happens when you compare a date and a string)
dd-mon-yyyy isn't a good format for querying since it deffers according to NLS_LANGUAGE better use mm for months numbers

So, either use @mvp's answer or do something like this:
SELECT *
FROM workingemployee
WHERE trunc(created_date) = to_date('20-01-2013', 'dd-mm-yyyy') - 10

