OraFAQ Forum: SQL & PL/SQL » Search data between months

Home » SQL & PL/SQL » SQL & PL/SQL » Search data between months (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Search data between months [message #657455]

Wed, 09 November 2016 04:53

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

John Watson
Messages: 8930
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.

Report message to a moderator

Re: Search data between months [message #657460 is a reply to message #657458]

Wed, 09 November 2016 05:07

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

Thanks John

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 11:16:01 CDT 2024