Oracle Virtualization and Cloud Consulting
view counter

Remember date contains time

Thanks to Scott Wesley for this story

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

Oracle Virtualization and Cloud Consulting
view counter

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);

Read the entire article at its source

view counter