My question is, I have a table which contains a column called myDateColumn (for example) which is of the type date:
I can confirm desc myTable
contains this line:
myDateColumn NOT NULL DATE
However, when I try to select all the data that is between certain dates, for example:
select * from myTable
where myDateColumn
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');
I get null results no matter what, even though I am sure some data exists within this specific date range.
The fix for it I discovered was to explicitly convert myDateColumn to a date:
select myDateColumn from myTable
where to_date(myDateColumn)
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');
which returns the correct results, but it is already a date type! Why do I need to explicitly convert a date column to date before doing the between
logical comparison?
The reason why I ask is because this select/ where will be part of a stored procedure selecting data within a specific date range to be run on a big table partitioned by date, with more than ten million rows, and if I can avoid explictly converting each myDateColumn of each row, then I might be able to save some query time, in theory.
Or is there a more correct way to run this comparison to select data within a date range?
Thank you.
Update: Leigh suggested in the answer to diagnose the table for something inconsistent with his query. The result of his query was:
myDateColumn TO_DATE(myDateColumn) TO_CHAR(myDateColumn,'DD-MON-YYHH.MI.SSPM') TO_CHAR(myDateColumn,'YYYY')
14-Feb-11 14-Feb-11 14-FEB-11 12.00.00 AM 0011
14-Feb-11 14-Feb-11 14-FEB-11 12.00.00 AM 0011
14-Feb-11 14-Feb-11 14-FEB-11 12.00.00 AM 0011
14-Feb-11 14-Feb-11 14-FEB-11 12.00.00 AM 0011
14-Feb-11 14-Feb-11 14-FEB-11 12.00.00 AM 0011
Indicating that the reason the between
clause was not working, is because none of the data would fall in the query interval, since the year was 0011, during the Roman Empire :)
Thanks guys.