CODEWITHSUNDEEP
sqloracle11g
Stanley Mungai
Stanley Mungai

Reputation: 4150

Select records for a certain year Oracle

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?

Upvotes: 13

Views: 61139

Answers (5)

Ananya Stitipragyan
Ananya Stitipragyan

Reputation: 21

select * from table_name where YEAR(date_column_name) = 2019

Upvotes: 0

Dinesh Kumar
Dinesh Kumar

Reputation: 11

select FIRST_NAME , to_char(hire_date, 'YYYY') YR FROM employees where to_char(hire_date, 'YYYY')= '2006'

Upvotes: 1

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

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

Upvotes: 9

prasanth
prasanth

Reputation: 21

select last_name,hire_date 
from employees
where extract(year from hire_date) = 2006;

Upvotes: 1

Kevin Bowersox
Kevin Bowersox

Reputation: 94429

Use the extract function to pull the year from the date:

select * from sales_table
where extract(YEAR from tran_date) = 2013

Upvotes: 43

Related Questions