SYSDATE function in SQL

Read this Oracle Tutorial Point article to know about SYSDATE and DATE arithmetic functions in SQL. The SYSDATE function in SQL is used to retrieve the current date and time, which Oracle stores in an internal numeric format with a wide range from January 1, 4712 BC to December 31, 9999 AD. SQL allows for date arithmetic, enabling users to add or subtract days and hours to/from dates, facilitating various date manipulations through simple arithmetic operations. Examples demonstrate how to implement SYSDATE and date arithmetic using SQL queries effectively. 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;

Key Points:

• SYSDATE is a key SQL function that returns the current date and time.
• Oracle dates range from January 1, 4712 BC to December 31, 9999 AD and are stored in a numeric format.
• The default input and display format for dates in Oracle is DD-MON-YY.
• Date arithmetic allows users to add or subtract specific numbers of days or hours to/from dates.
• SQL queries can utilize SYSDATE and perform operations such as adding days, subtracting days, or calculating intervals between dates.
• Examples of using SYSDATE demonstrate practical applications in retrieving and manipulating hire dates in employee records.
• The DUAL table is commonly used in SQL to select values like SYSDATE.

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;