Search code examples
sqloracle-databasedatetimeinner-joinwhere-clause

Proper record not displaying for data needed in oracle

I have a requirement where I want to show the relevant data which should not be greater than 6 months from todays date. SO I wrote the below query for the same but it is showing me the data of 2019 also.

select CR.CHANGEREQUESTID ,CR.CHANGEREQUESTNUMBER, CR.STATENAME, CR.NETWORKTYPE, CR.CREATEDON, 
CR.LASTMODIFIEDON,
NHQ.SAP_ID, NHQ.STATE, NHQ.NEW_LATITUDE, NHQ.NEW_LONGITUDE, NHQ.OLD_LATITUDE, NHQ.OLD_LONGITUDE
from CHANGEREQUESTS CR
inner join TBL_NHQ_CIRCLE_INFO NHQ
on CR.CHANGEREQUESTID = NHQ.CHNGREQUEST_ID
where CR.lastmodifiedon > add_months(sysdate, -12)
and CR.CHANGETYPEID=55;

Please suggest what is wrong here.

Solution

  • This condition gives you all record that are less than 1 year old:

    where CR.lastmodifiedon > add_months(sysdate, -12)

    If you wanted records older than 6 month, that would be:

    where CR.lastmodifiedon < add_months(sysdate, -6)

    On the other hand, if you want dates between 6 months ago and today:

    where CR.lastmodifiedon > add_months(sysdate, -6) and  CR.lastmodifiedon <= sysdate