How to correctly handle dates in queries constraints

Question

I am currently using the following query to get all the inspections done on june 2010:

select inspections.name
from inspections
where
  to_char(inspections.insp_date, 'YYYY') = 2010 and
  to_char(inspections.insp_date, 'MM') = 06;

but this feels kinda awkward. Wouldn't there be a better way of doing this? Looking at http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html it doesn't seem so. I am using Oracle, if it makes a difference.

Thanks

Answer 1

I like to use range comparison when possible since this can be used for index-scan by the optimizer:

select inspections.name
  from inspections
 where inspections.date >= DATE '2010-06-01'
   and inspections.date < DATE '2010-07-01'

Answer 2

I agree with Vincent's answer, but for completeness you could simplify yours to:

select inspections.name
from inspections
where
  to_char(inspections.insp_date, 'YYYYMM') = '201006';

That could even use an index, provided the index were:

create index x on inspections (to_char(insp_date, 'YYYYMM'));

Answer 3

select inspections.name
from inspections
where
  extract(year from inspections.insp_date) = 2010 and
  extract(month from inspections.insp_date) = 6;

Answer 4

Another option would be

SELECT inspections.name
  FROM inspections
 WHERE TRUNC( inspections.insp_date, 'MM' ) = date '2010-06-01';

From an efficiency perspective

• Vincent's solution can potentially use a range scan on an index on INSP_DATE (assuming that the table has many months of data such that index access would be the most efficient plan).
• My query can potentially use a function-based index on TRUNC( insp_date, 'MM' ) again assuming that the table has many months of data. If there is a large variation in the number of rows for different months, the optimizer's cardinality estimates may be slightly more accurate with this function-based index than they would be on a straight INSP_DATE index but that is highly unlikely to be important in a query this simple. It could come into play, however, if you start nesting this query elsewhere. If you need to index INSP_DATE for other reasons, however, maintaining two different indexes is potentially a time and space waster.
• Your initial query and Lev's query could both potentially use a composite function-based index (though you'd want to include an explicit TO_NUMBER or compare to strings rather than numbers to avoid implicit conversions). The composite index, though, is likely to be the least efficient to maintain (though we're talking here about relatively small differences) and strikes me as the least clean of the index alternatives.