Comparing with date in Oracle sql

Question

I have a column 'creation_date' which is of type 'date', when I am querying my table for distinct records based on 'creation_date' I am getting 6 records:

select distinct creation_date from test_table;

output:

06-APR-11
06-APR-11
28-MAR-11
06-APR-11
06-APR-11
18-MAR-11

In this output 6th April is displayed 4 times even when I used distinct in my query. Also when I am trying to find out all records which are matching with creation_date of 6th April 2011 I am not getting any results. Below is my query:

select * from  test_table where creation_date = to_date('06-APR-11','DD-MON-YY');

Please help me where I am doing wrong in these two queries.

Answer 1

The problem is twofold. Firstly the dates almost definitely have time-components. to_date('06-MAR-11','DD-MON-YY') is equivalent to 2011/03/06 00:00:00. If you use the TRUNC() function you will be able to see everything for that day:

select * 
  from test_table
 where trunc(creation_date) = to_date('06-MAR-11','DD-MON-YY');

I would not use the MON datetime format model. As I explain here it depends on your region and settings. It's safer to use a numeric month format model instead. Equally, always specify century as part of the year.

where trunc(creation_date) = to_date('06-03-YY11','DD-MM-YYYY');

Your second problem is almost definitely your NLS_DATE_FORMAT; it appears to not take into account the time, hence why you see 4 identical dates. This only governs the manner in which data is displayed not how it is stored.

You can change this using something like:

ALTER SESSION SET NLS_DATE_FORMAT = "DD/MM/YYYY HH24:MI:SS"

If I set up a test environment using the following:

create table test_table ( creation_date date );
insert into test_table values ( sysdate );
insert into test_table values ( sysdate - 0.01 );
alter session set nls_date_format = "YYYY/MM/DD";

You can see the data returned does not include time (though SYSDATE does):

SQL> select * from test_table;

CREATION_D
----------
2013/04/12
2013/04/12

Altering the NLS_DATE_FORMAT and performing the same SELECT, you now get a time component:

SQL> alter session set nls_date_format = "YYYY/MM/DD HH24:MI:SS";

Session altered.

SQL> select * from test_table;

CREATION_DATE
-------------------
2013/04/12 12:48:41
2013/04/12 12:34:17

Lastly, when trying to select today's date alone no rows will be returned:

SQL> select *
  2    from test_table
  3   where creation_date = to_date('20130412','yyyymmdd');

no rows selected

But, when using TRUNC() to compare on only the date portion of the field you get all your rows again:

SQL> select *
  2    from test_table
  3   where trunc(creation_date) = to_date('20130412','yyyymmdd');

CREATION_DATE
-------------------
2013/04/12 12:48:41
2013/04/12 12:34:17

To actually answer your second question, if you want unique dates you can re-use the TRUNC() function:

select distinct trunc(creation_date)
  from test_table