How to return today's date to a variable in Oracle

Question

I want to do this:

DECLARE @today as smalldatetime
SELECT @today = GetDate()

But i need an oracle translation

Answer 1

Oracle uses SYSDATE, and there's the ANSI standard CURRENT_TIMESTAMP (supported by both SQL Server and Oracle, besides others) to get the current date & time.

v_today DATE;

SELECT SYSDATE
  INTO v_today
  FROM DUAL;

...would be the equivalent to the TSQL you posted. Oracle uses the INTO clause for populating variables, where the variable data type must match the column position in the SELECT clause.

Answer 2

While not a strict translation, I prefer the following construction in Oracle:

v_today date; -- needs to go where variables are declared

v_today := sysdate; -- used where code is run.

Or even:

v_today date := sysdate;