Select records for a certain year Oracle

Question

I have an oracle table that store transaction and a date column. If I need to select records for one year say 2013 I do Like this:

select * 
  from sales_table
 where tran_date >= '01-JAN-2013'
   and tran_date <= '31-DEC-2013'

But I need a Straight-forward way of selecting records for one year say pass the Parameter '2013' from an Application to get results from records in that one year without giving a range. Is this Possible?

Answer 1

You can use to_date function

http://psoug.org/reference/date_func.html

select *
  from sales_table
 where tran_date >= to_date('1.1.' || 2013, 'DD.MM.YYYY') and 
       tran_date < to_date('1.1.' || (2013 + 1), 'DD.MM.YYYY')

solution with explicit comparisons (tran_date >= ... and tran_date < ...) is able to use index(es) on tran_date field.

Think on borders: e.g. if tran_date = '31.12.2013 18:24:45.155' than your code tran_date <='31-DEC-2013' will miss it