Top 10 Ways to Get Current Date and Time in Oracle DB

To get the current date and time on the Database Server Oracle has SYSDATE internal value which returns the current date from the operating system on which the database resides. The datatype of the returned value is DATE, and the format returned depends on the value of the NLS_DATE_FORMAT initialization parameter.

Examples to get the Current Date and Time

SQL> select sysdate from dual;
SYSDATE
---------
18-MAY-15 

The default format that the Oracle database uses is: DD-Mon-YY

SQL> SELECT TO_CHAR
(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "TODAY"
FROM DUAL; 
TODAY
-------------------
05-18-2015 01:34:25

To get the server TimeZone

SQL> SELECT sessiontimezone FROM dual;

SESSIONTIMEZONE
------------------------------------
+05:30

This will give you the system timezone in this format, Ex: Asia/Calcutta

To get the server time

SQL> SELECT systimestamp FROM dual;

SYSTIMESTAMP
----------------------------------------------------
18-MAY-15 01.42.27.939642 AM -04:00

To Change nls_date_format

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD';
select sysdate from dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'HH24:MI:SS';
select sysdate from dual;

 Get Time using the format mask

SQL> select TO_CHAR(sysdate, 'MM/DD/YYYY') TODAY from dual;
TODAY
----------
05/18/2015

SQL> select TO_CHAR(sysdate, 'HH24:MI:SS') TODAY from dual;
TODAY
--------
01:47:22

SQL> select TO_CHAR(sysdate, 'YYYY-MM-DD') TODAY from dual;
TODAY
----------
2015-05-18

Date is stored in the database as a number, you can perform calculations on it.

SQL>  SELECT
     SYSDATE Today,
      SYSDATE + 1 Tomorrow,
     SYSDATE - 1 Yesterday
     from dual;

TODAY        TOMORROW     YESTERDAY
-------------------------------------------------------------
18-MAY-15    19-MAY-15       17-MAY-15

Above given examples are very useful to find the current date and time from sql prompt of Oracle database. If you have more interesting examples, you can share with us.