SYSDATE function in SQL

Read this Oracle Tutorial Point article to know about SYSDATE and DATE arithmetic functions in SQL. Oracle stores dates in an internal numeric format. The range of the DATE in oracle is from January 1, 4712 BC to December 31, 9999 AD. The default display and input format for any date is DD-MON-YY.

• Century
• Year
• Month
• Day
• Hour
• Minute
• Second

SYSDATE:

SYSDATE is a data function that returns the current date and time. SYSDATE is generally selected upon a DUMMY table.

SQL> SELECT SYSDATE FROM DUAL;

DATE Arithmetic:
As database stores DATE as numbers, Arithmetic operations can be implemented. Number constants can be added or subtracted upon DATE. The operations those can be applied are,

• Date + Number --> Date: Adds number of days to a date
• Date - Number --> Date: Number of days subtracted from a date
• Date - Date --> Number of days: subtracts one date from another
• Date + Number/24 --> Date: Number of hours adds to a date

Examples:

SQL> SELECT SYSDATE, SYSDATE + 5 FROM DUAL;

SQL> SELECT SYSDATE, SYSDATE – 5, SYSDATE + 120/24 FROM DUAL;

SQL> SELECT ENAME, HIREDATE, HIREDATE + 5 FROM EMP;

SQL> SELECT ENAME, HIREDATE, HIREDATE - 5 FROM EMP;

SQL> SELECT ENAME, HIREDATE, HIREDATE – SYSDATE FROM EMP;

SQL> SELECT ENAME, (HIREDATE – SYSDATE)/7 WEEKS FROM EMP WHERE DNO=10;