Declare YESTERDAYS Date as a variable in Oracle

I think you're after:

select *from   my_tablewhere  disp_cret_dt >= trunc(sysdate-1);

That's assuming that disp_cret_dt is of datatype DATE or TIMESTAMP.

In Oracle, differences between two dates (which includes the time) are always returned as the number of days difference - and it can contain fractions of a day (eg. today at 12 noon - today at midnight = 0.5).

SYSDATE is Oracle's way of returning the current date+time.

TRUNC(dt, level) is the way you can truncate the date to whichever level you like - the default is day (which will just reset the time to midnight - 00:00), but you could do month (takes it back to the first of the month), hours etc etc.

Below is an equivalent code for oracle

declare yesterday  date;    begin    select to_char(sysdate-1,'dd/mm/yyyy hh:mi:ss') into yesterday from dual;    select * into var1,var2..varn from my_table     where disp_cret_dt>=yesterday;    end;

1.Dual is temporary table in oracle which contains one column named as dummy with data type of varchar2(1). For more Refer here.

2.The SELECT INTO clause of SQL is used to retrieve one row or set of columns from the Oracle database. The SELECT INTO is actually a standard SQL query where the SELECT INTO clause is used to place the returned data into predefined variables.

If you want to return three items you have to define three variables in our pl/sql block with respective data types after applying these changes to above code it looks

declare yesterday  date;v_item1 number;v_item2 varchar2(11);v_item3 date;    begin    select to_char(sysdate-1,'dd/mm/yyyy hh:mi:ss') into yesterday from dual;    select  item1, item2,item3 into v_item1,v_item2,v_item3 from my_table     where disp_cret_dt>=yesterday;Dbms_output.put_line('Item1: '||v_item1||'Item2: '||v_item2||'Item3: '||v_item3);--Displaying values    end;

Note: In the above code if your select query will returns more than one row for each yesterday value then it will throws an error. Because at a time a variable will holds one value. In that scenario we have to choose collections in oracle for more Refer here.

if you want to have "yesterday" in a seperate variable because you use it more than once in your code, assign "sysdate-1" to it:

declare  yesterday date := trunc(sysdate - 1);begin  select * from my_table where disp_cret_dt >= yesterday;end;