I am developing a oracle database for a transactions application. I have come across this problem on selecting date with month & year . i do understand that it is achievable in PL/SQL but i feel there must be a way with just using oracle statement.

Based on my research , below statement is to select month from each row in the table.

select to_char(sysdate, 'Month') from income

ive tried to modify it to my needs but to i failed. So far, i have came out with this statement but it does not work

select * from income where to_char(sysdate,month) = 'feb'

For an example, i will be able to select 'May 2012' instead of displaying all the records. Anybody did ever stumble this problem before?

Thank you in advance :)

3 Answers

Boneist On

Are you after something like:

select *
from   income
where  <date_column> >= to_date('01/05/2019', 'dd/mm/yyyy')
and    <date_column> <  to_date('01/06/2019', 'dd/mm/yyyy')

(replacing <date_column> with the name of the date column in your income table that you want to filter on)?

Community On

I think you can use the following query:

select *
from   income
where  to_char(<date_column>,'MON-RRRR') = 'MAY-2019';
Gordon Linoff On

If you want to pass in a string like 'May 2012', then I would recommend:

select i.*
from income i
where i.datecol >= to_date('May 2012', 'Mon YYYY') and
      i.datecol < to_date('May 2012', 'Mon YYYY') + interval '1' month;

That said, I think your application should turn the string value into a date range and you should use that range in your query:

select i.*
from income i
where i.datecol >= :datestart
      i.datecol < :dateend + interval '1 day';

I strong encourage you to avoid between with dates, particularly in Oracle. The date data type has a built-in time component, and that can throw off the comparisons.