Home » SQL & PL/SQL » SQL & PL/SQL » Search data between months (Oracle 11g)
Search data between months [message #657455] Wed, 09 November 2016 04:53 Go to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Hello,

I'm searching data like:

"Date" between "current_month-1" and "current_month+6"

where Date= 1.2.2017

I'm using below query for condition:

select  to_char( ADD_MONTHS( sysdate, -1 ) ,'MM-YYYY') from dual; // for last month data
select  to_char( ADD_MONTHS( sysdate, 6 ) ,'MM-YYYY') from dual;  // for current +6 months data

so final condition like:
to_char(date,'MM-YYYY') between to_char( ADD_MONTHS( sysdate, -1 ) ,'MM-YYYY') and to_char( ADD_MONTHS( sysdate, 6 ) ,'MM-YYYY') 


but the above condition is not working, please help me to correct above condition.

Thanks in advance

[Updated on: Wed, 09 November 2016 04:54]

Report message to a moderator

Re: Search data between months [message #657458 is a reply to message #657455] Wed, 09 November 2016 04:57 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
Your code is attempting to compare strngs. You need to compare dates. Get rid of those TO_CHAR calls.
Re: Search data between months [message #657460 is a reply to message #657458] Wed, 09 November 2016 05:07 Go to previous message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Thanks John
Previous Topic: Getting months in the current Fiscal Year in Oracle
Next Topic: How to log what parameter the API is passing to query
Goto Forum:
  


Current Time: Fri Oct 19 16:34:20 CDT 2018