Oracle get previous day records

You can remove the time part of a date by using TRUNC.

select field,datetime_field 
  from database
 where datetime_field >= trunc(sysdate-1,'DD');

That query will give you all rows with dates starting from yesterday. Note the second argument to trunc(). You can use this to truncate any part of the date.

If your datetime_fied contains '2011-05-04 08:23:54', the following date will be returned

trunc(datetime_field, 'HH24') => 2011-05-04 08:00:00
trunc(datetime_field, 'DD')   => 2011-05-04 00:00:00
trunc(datetime_field, 'MM')   => 2011-05-01 00:00:00
trunc(datetime_field, 'YYYY') => 2011-00-01 00:00:00

how about sysdate?

SELECT field,datetime_field 
FROM database
WHERE datetime_field > (sysdate-1)

SELECT field,datetime_field 
FROM database
WHERE datetime_field > (CURRENT_DATE - 1)

Its been some time that I worked on Oracle. But, I think this should work.

this

    SELECT field,datetime_field 
FROM database
WHERE datetime_field > (sysdate-1)

will work. The question is: is the 'datetime_field' has the same format as sysdate ? My way to handle that: use 'to_char()' function (only works in Oracle).

samples: previous day:

select your_column
from your_table
where to_char(sysdate-1, 'dd.mm.yyyy')

or

select extract(day from date_field)||'/'|| 
       extract(month from date_field)||'/'||
       extract(year from date_field)||'/'||
as mydate
from dual(or a_table)
where extract(day from date_field) = an_int_number and
      extract(month from date_field) = an_int_number and so on..

comparing date:

select your_column
from your_table
where
to_char(a_datetime_column, 'dd.mm.yyyy') > or < or >= or <= to_char(sysdate, 'dd.mm.yyyy')

time range between yesterday and a day before yesterday:

select your_column
from your_table
where
to_char(a_datetime_column, 'dd.mm.yyyy') > or < or >= or <= to_char(sysdate-1, 'dd.mm.yyyy') and
to_char(a_datetime_column, 'dd.mm.yyyy') > or < or >= or <= to_char(sysdate-2, 'dd.mm.yyyy')

other time range variation

select your_column
from your_table
where 
to_char(a_datetime_column, 'dd.mm.yyyy') is between to_char(sysdate-1, 'dd.mm.yyyy') 
and to_char(sysdate-2, 'dd.mm.yyyy')