CODEWITHSUNDEEP
sqloracle-database
Kenny
Kenny

Reputation: 1054

my query to produce results based on date range is returning null. Any ideas why?

I am running the following query:

select * 
  from myTable 
 where TO_CHAR(TO_DATE( transdate,'DD-MM-YY'),'MM/DD/YYYY')) Between
         TO_CHAR(TO_DATE('01-01-13','DD-MM-YY'),'MM/DD/YYYY')) AND 
         TO_CHAR(TO_DATE('10-07-13','DD-MM-YY'),'MM/DD/YYYY'))

is not getting any records from the db.

Any ideas what I am doing wrong?

transdate is in the format of 'dd-mm-yy'.

My query to produce results based on date range is returning null. Any ideas why?

Upvotes: 0

Views: 143

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269893

You can fix your problem by using proper date formats for comparing dates as strings:

 select * 
 from myTable 
 where TO_CHAR(TO_DATE( transdate,'DD-MM-YY'), 'YYYY-MM-DD')) Between
         TO_CHAR(TO_DATE('01-01-13','DD-MM-YY'), 'YYYY-MM-DD')) AND 
         TO_CHAR(TO_DATE('10-07-13','DD-MM-YY'), 'YYYY-MM-DD'));

But you should really just be doing:

select *
from myTable
where trunc(transdate) between to_date('2013-01-01', 'YYYY-MM-DD') and to_date('2013-07-10', 'YYYY-MM-DD');

Or, better yet:

select *
from myTable
where transdate >= to_date('2013-01-01', 'YYYY-MM-DD') and
      transdate < to_date('2013-07-11', 'YYYY-MM-DD');

Upvotes: 4

Related Questions