Current date and time in Oracle SQL-CodePudding

I want a SQL query to show the current date and time.

I have tried

SELECT CURRENT_TIMESTAMP;   --"FROM keyword not found where expected"
SELECT GETDATE();           --"FROM keyword not found where expected"
SELECT GETUTCDATE();        --"FROM keyword not found where expected"
NOW();                      -- Error report - Unknown Command

But none of them worked.

Requesting your help.

Thanks!

CodePudding user response：

Oracle supports ANSI standard variables CURRENT_TIMESTAMP and CURRENT_DATE with mandatory FROM clause -

SELECT CURRENT_TIMESTAMP FROM DUAL;

SELECT CURRENT_DATE FROM DUAL;

Alternatively, You can use Oracle specific date variable SYSDATE also -

SELECT SYSDATE FROM DUAL;

Demo.

CodePudding user response：

You probably want to handle the format of date/time fetched. For that you can use To_Char() function. Some of the options are:

Select
      SYSDATE "DB_FORMAT_DATE",
      To_Char(SYSDATE, 'dd-Month yyyy') "DATE_FORMAT_1",
      To_Char(SYSDATE, 'dd.mm.yyyy') "DATE_FORMAT_1",
      To_Char(SYSDATE, 'hh:mi:ss am') "TIME_FORMAT_1",
      To_Char(SYSDATE, 'hh24:mi:ss') "TIME_FORMAT_2",
      To_Char(SYSDATE, 'dd-Mon-yyyy hh24:mi:ss') "DATETIME_FORMAT_ANY"
From Dual

DB_FORMAT_DATE DATE_FORMAT_1     DATE_FORMAT_1 TIME_FORMAT_1 TIME_FORMAT_2 DATETIME_FORMAT_ANY
-------------- ----------------- ------------- ------------- ------------- --------------------
02-FEB-23      02-February 2023  02.02.2023    04:29:30 PM   16:29:30      02-Feb-2023 16:29:30

More about it here.