Remember date contains time

This is one thing I regularly mention to trainees, and recently I fell for the problem myself!

Dates may contain a time component.

Simple example, some might expect this statement to return a result (as I write this being 1st March) - it does not.
select * from dual where sysdate <= date '2012-03-01';

For the pure reason that sysdate returns century, year, month, hour, minute, second. And so may some of the dates stored in your database.

SQL> select to_char(sysdate,'dd-Mon-yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS')
------------------------------------------------------------
01-Mar-2012 20:49:28

Here is a simple test case where ideally I would get two rows -"now" & "midnight today", and the build-up to this scenario:

create table my_dates(descr varchar2(50), dt date);
insert into my_dates values ('yesterday', sysdate-1);
insert into my_dates values ('now', sysdate);
insert into my_dates values ('midnight today', trunc(sysdate));
insert into my_dates values ('midnight tomorrow', trunc(sysdate)+1);
insert into my_dates values ('tomorrow', sysdate+1);

-- all dates
select * from my_dates;

DESCR                                              DT                  
-------------------------------------------------- ---------------------
yesterday                                          29/02/2012 19:40:12   
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   
midnight tomorrow                                  02/03/2012 00:00:00   
tomorrow                                           02/03/2012 19:40:12   

-- equal to today
select * from my_dates
where dt = date '2012-03-01';

DESCR                                              DT                  
-------------------------------------------------- ---------------------
midnight today                                     01/03/2012 00:00:00   

-- still equal to today
select * from my_dates
where dt between date '2012-03-01'
         and     date '2012-03-01';

DESCR                                              DT                  
-------------------------------------------------- ---------------------
midnight today                                     01/03/2012 00:00:00   

-- better, but includes midnight tomorrow
select * from my_dates
where dt between date '2012-03-01'
         and     date '2012-03-01'+1;

-------------------------------------------------- ---------------------
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   
midnight tomorrow                                  02/03/2012 00:00:00   

-- up to one second before midnight
select * from my_dates
where dt between date '2012-03-01'
         and     date '2012-03-01'+1-1/86400;
DESCR                                              DT                  
-------------------------------------------------- ---------------------
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   

-- excluding the ability to use an index on dt
select * from my_dates
where trunc(dt) = date '2012-03-01';

DESCR                                              DT                  
-------------------------------------------------- ---------------------
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   

Here are other date related posts:

• Date format tolerance
• Interval issues
• Data densification - filling dates

Scott