CODEWITHSUNDEEP
sqloracle-databasedate
Raghav J M
Raghav J M

Reputation: 11

Oracle Date query between and equals

I am trying to execute below query,

select symbol,effdate,anrev,morev 
from perf_intra 
where effdate 
  between TO_DATE('01/11/2014','dd/mm/yyyy') 
      and TO_DATE('05/12/2015','dd/mm/yyyy') 
  and TO_CHAR(effdate,'dd/mm') = '31/03';

I have records which satisfies both the conditions. But query returns nothing, but when I tried the conditions separately it works.

Can anyone please help me to find the issue with my query.

EDIT:

Let me put my question in more clear way, Lets consider this,

Query

select symbol,effdate,anrev,morev
from perf_intra
where effdate between TO_DATE('25/11/2013','dd/mm/yyyy') and TO_DATE('05/12/2014','dd/mm/yyyy')

Sample Data

BMA 25-NOV-13 12.00.00.000000000 AM 12.3977 11.2296
BMA 26-NOV-13 12.00.00.000000000 AM 12.4174 11.2468
BMA 27-NOV-13 12.00.00.000000000 AM 12.3991 11.2307
BMA 29-NOV-13 12.00.00.000000000 AM 12.3975 11.2294
BMA 02-DEC-13 12.00.00.000000000 AM 12.3631 11.1991

Table Description 

 Name    Null Type              
---------------------
SYMBOL       VARCHAR2(20 CHAR) 
EFFDATE      TIMESTAMP(6)      
ANREV        VARCHAR2(20 CHAR) 
MOREV        VARCHAR2(20 CHAR) 
REVAL        VARCHAR2(20 CHAR)

I want all the records whose effdate is (Mar 31) and between (25/11/2013) to (05/12/2014)

Guys,

Following solution worked,

select symbol,effdate,anrev,morev 
from perf_intra 
where effdate between 
      TO_DATE('25/11/2013','dd/mm/yyyy') and 
      TO_DATE('05/12/2014','dd/mm/yyyy')  
  and EXTRACT(month FROM effdate)=3 
  and EXTRACT(day FROM effdate)=31

Thank you all

Upvotes: 1

Views: 2019

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Here is the where written in an easier to follow date format:

where effdate between date '2014-11-01' and date '2015-12-05' and
      TO_CHAR(effdate, 'mm-dd') = '03-31';

The only date that meets both conditions is '2015-03-31', so you might as well write:

where effdate = date '2015-03-31'

or perhaps:

where trunc(effdate) = date '2015-03-31'

My guess is that you have no rows where effdate is '2015-03-31'.

Upvotes: 1

Related Questions