CODEWITHSUNDEEP
oracle-databaseperformancedatetype-conversion
Marcus
Marcus

Reputation: 3869

Conversion of number to date affect the performance in oracle

I have below query where the DT_ID is number data type column and the value stored in this column is date in the format 'YYYYMMDD'.

In my below query I want to get the previous week data from Monday till Sunday for weekly reporting and this query works very fast.

select  ID,NAME
from TEST_REPORT
where 
DT_ID between 20170904 and 20170910;

But this is hardcoded date and I want to make it dynamic. I tried with this query:

select  ID,NAME
from TEST_REPORT
where 
DT_ID>= next_day(trunc(sysdate), 'MONDAY') - 14 and
          DT_ID< next_day(trunc(sysdate), 'MONDAY') - 7;

But it gives this error:

expecting Number and got date

When I convert this number to date like below query it's resulting a lot of performance issue. Is there any other way Ican make it dynamic with better performance?

select  ID,NAME
from TEST_REPORT
where 
to_date(DT_ID,'YYYYMMDD') >= next_day(trunc(sysdate), 'MONDAY') - 14 and
          (DT_ID,'YYYYMMDD') < next_day(trunc(sysdate), 'MONDAY') - 7;

Upvotes: 2

Views: 154

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132580

You can convert the date values to strings and then to numbers:

select  ID,NAME
from TEST_REPORT
where DT_ID >= to_number (to_char (next_day(trunc(sysdate), 'MONDAY') - 14,
                           'YYYYMMDD'))
  and DT_ID < to_number (to_char (next_day(trunc(sysdate), 'MONDAY') - 7,
                           'YYYYMMDD'));

Upvotes: 3

Related Questions