How to handle dates in queries correctly

Question

I am currently using the following query to get all checks done in June 2010:

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

but it seems awkward. Wouldn't there be a better way to do this? Looking at http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html , it seems like this is not the case. I use Oracle if that matters.

thanks

Answer 1

I like to use range comparison when possible, as it can be used to optimize index scans:

 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 can simplify yours:

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

It can even use an index if the index was:

 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 variant:

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

In terms of efficiency

• Vincent’s solution can potentially use range scanning on the INSP_DATE index (assuming the table has many months of data, so accessing the index will be the most efficient plan).
• My query could potentially use a functional index on TRUNC (insp_date, "MM"), assuming the table has many months of data. If within a few months there is a large change in the number of rows, the optimizer power estimates may be a little more accurate with this function-based index than they would be on the direct INSP_DATE index, but this is unlikely to be important in a query that's simple. However, this can come in handy if you start using this query elsewhere. However, if you need to index INSP_DATE for other reasons, maintaining two different indexes is potentially a waste of time and space.
• Your initial query and the Leo query could both potentially use a compound functional index (although you would like to include explicit TO_NUMBER or compare strings rather than numbers to avoid implicit conversions). A component index is likely to be the least efficient to maintain (although we are talking about relatively small differences) and calls me the least clean of the index alternatives.