How to set current date to alias in Oracle

Question

In SQL Server we can declare a variable and set the date to that variable in query like this:

GETDATE() AS currentDate

How is this accomplished in Oracle?

Your title doesn't reflect the code in your question as Lukasz has explained below. Which is it... an alias or a variable that you need? Because both have been answered on Stackoverflow. [Variable](https://stackoverflow.com/questions/4925144/how-to-return-todays-date-to-a-variable-in-oracle)and [Alias](https://stackoverflow.com/questions/8451195/is-the-as-keyword-required-in-oracle-to-define-an-alias) — S3S, Aug 15 '19 at 13:52
In SQLServer the GETDATE and GETUTCDATE function return the current server time with millisecond precision. To get the same in oracle you'll need to use the SYSTIMESTAMP function. Example: `SELECT SYSTIMESTAMP FROM DUAL` . If seconds precision is all you need you can use SYSDATE . DATETIME returned from sysdate is slightly easier to work with than a TIMESTAMP but primarily your choice should be driven by the precision required — Caius Jard, Aug 15 '19 at 20:46

score 2 · Accepted Answer · answered Aug 15 '19 at 13:49

2

GETDATE() AS currentDate is a column alias:

SELECT SYSDATE AS currentDate FROM dual

Variable declaration inside anonymous block:

DECLARE 
   currentDate DATE;
BEGIN
   currentDate := SYSDATE;
END;

answered Aug 15 '19 at 13:49

Lukasz Szozda

score 1 · Answer 2 · answered Aug 15 '19 at 13:53

1

You can use INTO in a query in PL/SQL block and in your case, even you can assign it directly in PL/SQL block.

Declare
lv_date1 date;
lv_date2 date;
begin
select sysdate into lv_date1 from dual; --method 1
lv_date2 := sysdate; -- method 2
end;

Cheers!!

answered Aug 15 '19 at 13:53

Popeye

3

Using `select ... into ... from dual;` is just useless typing. – Wernfried Domscheit Aug 15 '19 at 14:03
Yes, But this is to provide all the methods to OP for assigning value to a variable – Popeye Aug 15 '19 at 14:04
Yes, that will happen but currently, my aim is to provide all the methods available for doing the task to OP. – Popeye Aug 15 '19 at 14:06
This was necessary Back In The Day (and before my time, which is going some :-) but is pointlessly obfuscatory today. [See my question regarding this issue and its answers for further information](https://stackoverflow.com/questions/34418779/using-select-to-call-a-function). – Bob Jarvis - Слава Україні Aug 15 '19 at 14:23
Thanks @BobJarvis, Learned the thing from your question. – Popeye Aug 15 '19 at 14:29

2 Answers2