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.
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.
created_date is not really of DATE type, but VARCHAR. This is really bad idea, especially for speed and indexing - please convert this to real DATE. But, you can use this expression as workaround: to_date('25-DEC-2012', 'dd-mon-yyyy')to_date('25-DEC-2012', 'dd-mon-yyyy') to convert your input string into real date typecreated_date, it should work very fastlike and not = ?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 numbersSo, 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
created_date, thus it will be using full table scan - very slow, especially if table is big.
function based indexes or by using create_date >= to_date('20-01-2013 000000', 'dd-mm-yyyy hh24miss') - 10 and create_date >= to_date('20-01-2013 235959', 'dd-mm-yyyy hh24miss') - 10SELECT * FROM workingemployee WHERE created_date > sysdate - INTERVAL '10' DAY;
created_date?likedoes not make any sense for a DATE