Declare YESTERDAYS Date as a variable in Oracle

Question

I'm very new to using Oracle (I'm using TOAD 11.6), I would like to turn this code into something that would work in Oracle, how do I do it?!

declare @yesterday  datetime
set     @yesterday  =   (select cast(cast(getdate() as varchar(12)) as datetime)-1)

select  *
from    my_table
where   disp_cret_dt    >=  @yesterday

Thanks in advance!!

Answer 1

I think you're after:

select *
from   my_table
where  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.

Answer 2

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.