Check the data and time at Session or Database level in Oracle

Following function return the value according to session or database servers:

CURRENT_DATE : Session
CURRENT_TIMESTAMP: Session
LOCALTIMESTAMP: Session
SYSDATE: Database server
SYSTIMESTAMP: Database server

— At Database Level:

SQL> select sysdate from dual;

SYSDATE
--------------------
07-JAN-2022 22:48:53

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'HH24:MI:SS';
Session altered.

SQL> select sysdate from dual;
SYSDATE
--------
09:35:17

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
08-JAN-22 07.42.31.495000 PM +05:30

-- We can also use to_char for convert the format and value:
select to_char(sysdate,'HH24:MI:SS') from dual;
TO_CHAR(
--------
19:45:04

select to_char(sysdate,'YYYY MM DD') from dual;
TO_CHAR(SY
----------
2022 01 08

— At Session Level:

SQL> SELECT CURRENT_TIMESTAMP FROM dual;

CURRENT_TIMESTAMP
---------------------------------------------
08-JAN-22 07.37.09.555000 PM +05:30

SQL> Select current_date from dual;

CURRENT_D
---------
08-JAN-22

SQL> Select LOCALTIMESTAMP from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
08-JAN-22 07.43.41.216000 P