Oracle sysdate function is used to check Oracle dates and time in the database. TO_CHAR function is used to convert sysdate into proper dates in Oracle. To get current date and time in Oracle 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.
How to check the Current Date and Time in Oracle
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.