CODEWITHSUNDEEP
sqloracle-databasejdbcoracle10g
user2909784
user2909784

Reputation:

How do we select data based on month and year in oracle query

I have did my research and I found that it can be done in MSQL with 

SELECT * FROM sales WHERE month(sellDate) = 3 AND YEAR(sellDate) = 2017

But when I tried with Oracle , It returns me 

ORA-00904: "YEAR": invalid identifier

Is there any Way I could call data based on month and year in oracle query ?

Solved

Thank you so much , I have also came out with a solution

This is how I did it .. :)

select * from sales where to_char(sell,'MM')='09' AND to_char(sell,'YYYY')='2018';

Upvotes: 2

Views: 18213

Answers (5)

user2805507
user2805507

Reputation:

Basically, input this code in your structure. Hope this helps.

 select * from sales where to_char(sell,'MM')='09' AND to_char(sell,'YYYY')='2018';

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191235

You can use extract or to_char, but as those are acting on the table column value they will cause every value in the table to be converted for comparison, which is wasteful and prevents any index on that column being used.

It would be better to convert your target year and month to match the data type of the column, and then use a range:

SELECT * FROM sales
WHERE sellDate >= date '2017-03-01'
AND sellDate < date '2017-04-01'

Or if the year and month are variables (so you can't use a date literal) you can use to_date() to convert the year and month to a date, and add_months() to find the start of the following month, e.g.:

SELECT * FROM sales
WHERE sellDate >= to_date('2017' || '03', 'YYYYMM')
AND sellDate < add_months(to_date('2017' || '03', 'YYYYMM'), 1)

and if you are passed the year and month as numbers instead of strings you can explicitly convert those to string with to_char.

Upvotes: 4

zarruq
zarruq

Reputation: 2465

You can use EXTRACT to get month and year from date. For Example

SELECT Extract(YEAR
               FROM CURRENT_DATE),
       EXTRACT(MONTH
               FROM CURRENT_DATE)
FROM dual;

It will generate result as below.

enter image description here

So the query you posted in question can be re-written as below.

SELECT *
FROM sales
WHERE EXTRACT(MONTH
              FROM sellDate) = 3
  AND Extract(YEAR
              FROM sellDate) = 2017;

You can check the demo here

Upvotes: 2

Nj3
Nj3

Reputation: 119

Try using Extract Please ensure sellDate is a date column or change it to date using to_date

SELECT * FROM sales WHERE EXTRACT(MONTH from sellDate) = 3 and EXTRACT(YEAR from sellDate) = 2017;

Upvotes: 0

Ori Marko
Ori Marko

Reputation: 58774

You can use to_char to get year/month:

SELECT * FROM dual WHERE to_char(sysdate,'MM') = 8 AND to_char(sysdate,'YYYY') = 2017

Upvotes: 0

Related Questions