CODEWITHSUNDEEP
oracle-databasetimestamp
V.R.Manivannan
V.R.Manivannan

Reputation: 31

oracle select query to fetch records of last 30 days without timestamp and no trunc should be used

oracle select query to fetch records of last 30 days without timestamp and trunc function should not be used

select * from table where enterdate between today date and last 30 day before

I used select sysdate,(sysdate-30) from dual;

But the time stamps is getting considered,but I want only date to be considered

And if I use trunc((sysdate)-30)-6) ,this trunc function was reducing the performace lot during execution for more records

hence index is used

Upvotes: 0

Views: 3556

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

select * from table where enterdate between TRUNC(SYSDATE-30) and TRUNC(SYSDATE) ;

Any function call on a table column could cause impact(Unless a function based index is over it), but not on Values. Now, when we do TRUNC(sysdate) , this happens only once and involves very negligible cost , since it doesnt work on a Column.

TRUNC(SYSDATE) would nullify the TIME Element in current time will '00:00:00' . So the BETWEEN operation is perfect! and your INDEX's range scan would be happening

Upvotes: 2

Related Questions