Using SYSDATE() without DUAL table

Question

I'm surprised this question hasn't been asked. I must be missing something very obvious here, and I'm prepared for all the down votes and the 3 second answer.

In MSSQL you're able to call GETDATE() without having to depend on a database table but in Oracle SYSDATE has to come from DUAL. Is there no way to get the current date in Oracle without having to go through DUAL?

EDIT

Clearly, this doesn't have much to do with SYSDATE but more with my incorrect usage of it. See the following WHERE clause:

WHERE (extract(YEAR from ORDER_DATE) = (case when extract(month from SYSDATE()) < 11 then extract(year from SYSDATE()) else extract(year from SYSDATE())+1 end)

The above will not work, and from the answers here it seems that adding dual in there is not recommended. What's the most optimal solution here?

Answer 1

Your question is about sysdate, but it really has nothing to do with sysdate.

In Oracle, all select queries require a from clause. The from clause has to contain something. So, once upon a time, someone at Oracle invented dual, along with its very awkward name, as a built-in table with exactly one row. Wikipedia even has an entry on this subject.

Not all databases require a from clause for the select -- SQL Server, MySQL, and Postgres, for instance, all allow select without one.

You can use a scalar expression, such as sysdate anywhere in a query where a scalar expression is allowed. You can repeat it as many times as you like, quite independent of what is in the from clause.

You can embed a scalar expression in a subquery in any database. For instance,

select (select sysdate from dual)
from dual

and:

select (select getdate())

Are both valid expressions. However, this is almost always bad practice and should almost always be avoided. (The one exception that I can think of is using a constant in a window functions ordering clause in SQL Server to prevent the overhead of sorting, such as row_number() over (order by (select null))).

Answer 2

You can just use SYSDATEwithout any explicit reference to the DUAL table.

Something along the lines of this: INSERT INTO SOME_TABLE(some_date) VALUES (SYSDATE)

Welcome to Oracle, where everything is baffling and contrary to common sense.

Answer 3

Is there no way to get the current date in Oracle without having to go through DUAL?

Sysdate is a pseudo-column, which we can think of as a a function which returns the current date time. So it can used like any other function. Yes, in a query projection like ...

 select sysdate from dual;

...or ...

insert into sometable (id, date_created)
values (id_seq.nextval, sysdate);

But also in a where clause. Employees hired in the last thirty days:

 select * from emp
 where hire_date > sysdate - 30

To find employees who were hired this year we could do this:

 select * from emp
 where extract(year from hire_date) = extract(year from sysdate)

...but this might perform better

 select * from emp
 where hire_date >= trunc(sysdate, 'YYYY') -- resolves to 2019-01-01
 /

And in PL/SQL:

declare
    ld date;
begin
    ld := trunc(sysdate); 
end;

Answer 4

Avoid writing () after sysdate.

Example - using your CASE in the WHERE clause (without using DUAL):

create table T ( dt date ) ;

insert into T ( dt ) values ( sysdate ) ;


-- just write SYSDATE, not SYSDATE()
select *
from T   
where ( extract ( year from dt ) ) = ( 
  case 
    when extract( month from sysdate ) < 11 then
      extract( year from sysdate ) 
    else
      extract( year from sysdate ) + 1
  end 
) ;

-- output
DT       
---------
23-MAY-19

DBfiddle