Re: comparing a date?

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Wed, 21 Jun 2006 09:39:10 -0400
Message-ID: <T5GdnQlp3I-Y0QTZnZ2dnUVZ_vCdnZ2d@comcast.com>

"Mark Harrison" <mh_at_pixar.com> wrote in message news:y_Zlg.69786$4L1.61885_at_newssvr11.news.prodigy.com...
: How do I compare a date to be equal to a particular day?
:
: For example, here's some dates in the all_users table:
:
: select * from all_users;
:
: USERNAME USER_ID CREATED
: ------------------------------ ---------- ---------
: MAB 235 20-JUN-06
: MPLANCK 234 20-JUN-06
: TBEST 233 20-JUN-06
:
: but if I just check for date equality, I don't match
: any rows:
:
: SQL> select * from all_users where created='20-JUN-06';
:
: no rows selected
:
: This query does the right thing:
:
: select * from all_users where created>'20-JUN-06' and created<'21-JUN-06';
:
: What's the proper way to compare a date column for a particular
: day, without respect to the time?
:
: Thanks!
: Mark
:
: --
: Mark Harrison
: Pixar Animation Studios

date comparisons are not too difficult, but unfortunately are often done incorrectly -- probably due in part to the examples included in most Oracle SQL 101 texts that perpetuate bad practice

two important points that were hinted at but not fully explained in the other posts:

as pointed out, Oracle DATE datatypes contain a time element with a granularity of seconds -- but they are not timestamps, that is a different datatype, with sub-second granularity.
as anytime pointed out, applying a a function (or an expression) to a column in a WHERE clause predicate can prevent the optimizer from using an index in resolving the query -- unless the index is a function based index that uses the same expression

not mentioned is that Oracle does implicit date to char (and other datatype) conversions, and the date conversion format is based on the current setting of NLS_DATE_FORMAT -- which can be changed globally by the DBA or for the session by the user (or code run by the user)

so in MH's queries:

the first one returns no rows because oracle does implicit date conversion based on the default date format, and the string value converted does not include a time element -- if a user had been created exactly at midnight on 6/20, that row would, however, be returned.

the 2nd one happens to work because oracle converts each string to a midnight date -- however, it would not return any user created at exactly midnight on 6/20

the 2nd one is the closest to the correct way to do the query, but will break as soon as the NLS_DATE_FORMAT setting is changed:

SQL> select * from all_users where created>'21-JUN-06' and created<'22-JUN-06';

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
PIXAR                                  61 21-JUN-06

SQL> alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';

Session altered.

SQL> select * from all_users where username = 'PIXAR';

USERNAME                          USER_ID CREATED
------------------------------ ---------- -------------------
PIXAR                                  61 06/21/2006 09:24:53

SQL> select * from all_users where created>'21-JUN-06' and created<'22-JUN-06';
select * from all_users where created>'21-JUN-06' and created<'22-JUN-06'

ERROR at line 1:
ORA-01843: not a valid month

so, the correct ways to search a DATE datatype that includes the time element (has not been truncated on entry) for a value that does not include a time element are either:

SQL> select * from all_users
2 where created >= to_date('2006-06-21','YYYY-MM-DD') 3 and created < to_date('2006-06-22','YYYY-MM-DD');

USERNAME                          USER_ID CREATED
------------------------------ ---------- -------------------
PIXAR                                  61 06/21/2006 09:24:53

SQL> select * from all_users
2 where trunc(created) = to_date('2006-06-21','YYYY-MM-DD');

USERNAME                          USER_ID CREATED
------------------------------ ---------- -------------------
PIXAR                                  61 06/21/2006 09:24:53

realizing that if an index is created on the truncated DATE datatype column, it sould be a function-based index (not possible in this example, since we're using a data dictionary view)

you can use the date format (and corresponding mask) of your choice -- however, you can also use the ANSI 'DATE' keyword instread of TO_DATE() if your version of oracle supports it (9i+, perhaps 9iR2+):

SQL> select * from all_users
2 where trunc(created) = date '2006-06-21';

USERNAME                          USER_ID CREATED
------------------------------ ---------- -------------------
PIXAR                                  61 06/21/2006 09:24:53

++ mcs Received on Wed Jun 21 2006 - 08:39:10 CDT