CODEWITHSUNDEEP
sqloracle-database
Bala
Bala

Reputation: 23

SQL query to select only year from date in inner join

I have 2 tables:

table1_name "emp"  contains "hiredate" column.
table2_name "dept" contains "dname" column.

Below is the sql code:

select extract(year from e.hiredate), 
       dname 
from emp e inner join dept d on e.deptno = d.deptno;

"deptno" is the common column for both the tables;

But i need only specific year(1980) to select from the date column "hiredate".

please someone help me

Upvotes: 0

Views: 2307

Answers (2)

Littlefoot
Littlefoot

Reputation: 142753

Use the same EXTRACT in the WHERE clause as you use it in SELECT, such as 

SQL> select extract(year from e.hiredate) yr,
  2         d.dname
  3  from emp e inner join dept d on e.deptno = d.deptno
  4  where extract(year from e.hiredate) = 1980;

        YR DNAME
---------- --------------
      1980 RESEARCH

SQL>

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

You should use WHERE:

select  dname 
from emp e 
inner join dept d 
  on e.deptno=d.deptno
WHERE e.hiredate >= DATE '1980-01-01' AND e.hiredate < DATE '1981-01-01';
-- index on emp(hiredate) woould increase performance

Upvotes: 3

Related Questions