[oracle] Oracle get previous day records

Ok I think I'm getting the previous year instead of the previous day, but I need to previous day.

SELECT TO_DATE(TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD'),'YYYY-MM-DD') - 1 FROM Dual

I'm comparing it to a datetime stamp in this format and wish to get all the rows from the previous day.

YYYY-MM-DD HH:MM:SS

So I'm trying something like this

SELECT field,datetime_field 
FROM database
WHERE datetime_field > TO_DATE(TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD'),'YYYY-MM-DD') - 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.

Im a bit confused about this part "TO_DATE(TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD'),'YYYY-MM-DD')". What were you trying to do with this clause ? The format that you are displaying in your result is the default format when you run the basic query of getting date from DUAL. Other than that, i did this in your query and it retrieved the previous day 'SELECT (CURRENT_DATE - 1) FROM Dual'. Do let me know if it works out for you and if not then do tell me about the problem. Thanks and all the best.

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')

I think you can also execute this command:

select (sysdate-1) PREVIOUS_DATE from dual;

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

Simple solution and understanding

To answer the question:

SELECT field,datetime_field 
FROM database
WHERE TO_CHAR(date_field, 'YYYYMMDD') = TO_CHAR(SYSDATE-1, 'YYYYMMDD');

Some explanation

If you have a field that is not in date format but want to compare using date i.e. field is considered as date but in number format e.g. 20190823 (YYYYMMDD)

SELECT * FROM YOUR_TABLE WHERE ID_DATE = TO_CHAR(SYSDATE-1, 'YYYYMMDD') 

If you have a field that is in date/timestamp format and you need to compare, Just change the format

SELECT TO_CHAR(SYSDATE-1, 'YYYY-MM-DD HH24:MI:SS')  FROM DUAL

IF you want to return it to date format

SELECT TO_DATE(TO_CHAR(SYSDATE-1, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS NEW_DATE  FROM DUAL

Conclusion.

With this knowledge you can convert the filed you want to compare to a YYYYMMDD or YYYY-MM-DD or any year-month-date format then compare with the same sysdate format.