CODEWITHSUNDEEP
oracle-databaseoracle11g
Gans
Gans

Reputation: 171

How to select data between yesterday at specific time to today specific time in oracle?

I have one Requirement where I have to show the records between specific date and time every day eg. between yesterday 9 Am to Today 9 Am 

Select * from sales where saledate between '24-OCT-17 09:00:00' to '25-OCT-17 09:00:00'

in oracle.

but not getting idea how to do this.

Upvotes: 4

Views: 8708

Answers (2)

user5683823
user5683823

Reputation:

9 AM today can be coded as

trunc(sysdate) + 9/24

and 9 AM yesterday as

trunc(sysdate) - 1 + 9/24

Alternatively,

trunc(sysdate) + interval '9' hour

and

trunc(sysdate) - interval '1' day + interval '9' hour

The calculations for "yesterday" can be simplified ( - 15/24 instead of - 1 + 9/24, and similarly for interval ) - but it is better to write the code the way I did, since it is clearer - easier to understand and maintain.

trunc(sysdate) means midnight (00:00:00) at the beginning of today. In date arithmetic, 1 means one day; 9/24 means 9 hours.

The WHERE clause may be written as

where saledate >= trunc(sysdate) - 1 + 9/24 and saledate < trunc(sysdate) + 9/24

Upvotes: 9

Cyrille MODIANO
Cyrille MODIANO

Reputation: 2376

you need to convert you strings to date :

Select * from sales 
where saledate between to_date('24-OCT-17 09:00:00','dd-MON-yy hh24:mi:ss') and to_date('25-OCT-17 09:00:00','dd-MON-yy hh24:mi:ss');

Upvotes: 1

Related Questions