Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Why are you using 2-digit years? Have we not learned anything from Y2K? –  Colin 't Hart Oct 23 '12 at 11:17
1  
I suggest using date literals in ISO format as: between date '2011-02-13' and date '2011-02-15' –  Colin 't Hart Oct 23 '12 at 11:26
    
you know, a lot of programmers now were not around to fix programming issues in 2000.... So I foresee a repeat in 2100, good eye. –  fa1c0n3r Oct 24 '12 at 2:29

2 Answers 2

up vote 1 down vote accepted

Your belief that this should not be necessary is correct based on your assumptions. Normally this means the assumptions should be rechecked (unless there is data corruption or a bug). If you run the following you should get the same results from the first query as the second:

DROP TABLE t1;
CREATE TABLE t1 AS (
   SELECT to_date('01-FEB-2011','DD-MON-YYYY')+level myDateColumn 
   FROM dual CONNECT BY level <=120);

select * from t1 
where myDateColumn 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

select myDateColumn from t1 
where to_date(myDateColumn) 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

To check some of the assumptions, can you show the results of the following query?

SELECT myDateColumn, to_date(myDateColumn)
   , to_char(myDateColumn,'DD-MON-YY HH.MI.SS PM'), to_char(myDateColumn,'YYYY') 
FROM myTable
WHERE to_date(myDateColumn)
BETWEEN to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

The first three columns should all show identical information and the last should verify that the year is correct.

share|improve this answer
    
Leigh... you were right to check this... posting the update in the question. The entire table is actually vulnerable to the Y2K problem... (or Y2100 I guess ) –  fa1c0n3r Oct 24 '12 at 2:21

All dates in Oracle have a time component.

I'm pretty sure you want all rows between the 13th and the 15th included. However, when you write:

BETWEEN to_date('13-02-2011', 'DD-MM-YYYY') AND TO_DATE('15-02-2011', 'DD-MM-YYYY')

you select only all date up to the 15-02-2011 at midnight. All rows later that day won't be selected.

What is happening is that when you use TO_DATE on a date, it will be converted to VARCHAR2 and then converted back to a date. The net effect will be a truncation to your date format as shown:

SQL> alter session set nls_date_format='dd/mm/yyyy';

Session altered.

SQL> select to_char(to_date(sysdate), 'dd/mm/yyyy hh24:mi:ss') trunc,
  2         to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') not_trunc
  3    from dual;

TRUNC               NOT_TRUNC
------------------- -------------------
23/10/2012 00:00:00 23/10/2012 15:15:32

So your two clauses are not equivalent at all. The second one selects the whole day feb, 15th whereas the first one only selects the first second of the day.

I suggest you use the following constructions when writing date range:

WHERE dat >= to_date('13-02-2011', 'DD-MM-YYYY')
  AND dat < to_date('16-02-2011', 'DD-MM-YYYY')

Also don't use MON or DAY format in your code since they are dependent upon language. Don't use YY since this can be confusing (ever heard of the Y2k bug?). Both formats are ok to display information to your users of course.

share|improve this answer
    
I had commented that this was incorrect because your query from dual for me was returning a time portion. The difference, and the critical component to your likely correct answer, is the nls_date_format setting. –  Leigh Riffel Oct 23 '12 at 14:05
    
@Leigh I have found that often to_date(date) goes unnoticed in code review but is a bug waiting to happen (because the behaviour is dependent upon an NLS setting) –  Vincent Malgrat Oct 23 '12 at 14:16
    
Indeed, to_date/to_char should always have a format specified. –  Leigh Riffel Oct 23 '12 at 14:18
    
I see.. I am looking at your solution, so far still encountering the same issue, the suggested solution produces a null result, doesn't matter what the interval is for some reason... but when I convert myDateColumn explicitly, that seemed to do it.... Looking at Leigh Riffel's solution as well, we may have come upon some new information... YTK bug related! :) –  fa1c0n3r Oct 24 '12 at 2:03
    
Thank you for the contribution as well, in the final query version, have noted the intricacy of the midnight setting of the to_date... the major issue was that the table had a fundamentally outdated date formatting style... –  fa1c0n3r Oct 24 '12 at 3:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.