Oracle pass today's value to a variable and use it-CodePudding

I want to use today as a variable and use it in a query like this:

  select record_date, 
         another_column
    from table
   where record_date = v_today
order by record_date;

Where v_today is the desired variable. How can I do it?

CodePudding user response：

If you want to filter rows by today's date you can use current_date, as in:

select record_date, another_column
from table
where record_date = trunc(current_date)
order by record_date;

CodePudding user response：

It's not clear if your problem is in passing a variable, or in how to compare dates. If you always want 'today', then there is no need to use a declared variable. Just use SYSDATE. And since DATE and TIMESTAMPS always contain a time component, you need to strip that out with TRUNC:

select record_date,
       another_column
from my_table
where trunc(record_date) = trunc(sysdate)
;

If you want 'yesterday', then subtrace 1 (day) from sysdate:

select record_date,
           another_column
    from my_table
    where trunc(record_date) = trunc(sysdate - 1 )
    ;