Oracle System Date

Oracle System Date | Here we will discuss how to get Oracle System date, and what is sysdate in oracle? What does select sysdate from dual? What is the default Oracle sysdate format?

In the Oracle database, the SYSDATE function is used to get the current date and time of the operating system in which Oracle database software is installed. In distributed SQL statements, this function returns the date and time set for the operating system of our local database.

Note:- We cannot use the SYSDATE function in the condition of a CHECK constraint.

Parameter and return value of SYSDATE function,

• It doesn’t accept any parameter.
• It returns the DATE value and the format returned depends on the value of the NLS_DATE_FORMAT initialization parameter.

Oracle sysdate format

The default setting of NLS_DATE_FORMAT is DD-MON-YY which returns a 2-digit day, a three-character month abbreviation, and a 2-digit year. Example:- 10-JAN-25

Select SYSDATE From Dual

To get the current date of the operating system we have to use “SELECT SYSDATE FROM DUAL” query.

SELECT SYSDATE FROM dual;

Output:-

SYSDATE
---------
19-AUG-20

To get both the date and time of the current operating system, the TO_CHAR() function can be used with different formats. The Syntax for this purpose is,

Syntax:-
SELECT TO_CHAR(SYSDATE, format_with_time)) FROM dual;

The different formats are,
DD-MM-YYYY HH:MM:SS
DD-MM-YY HH:MM:SS
DD-MON-YYYY HH:MM:SS
DD-MON-YY HH:MM:SS
MM-DD-YYYY HH:MM:SS
MM-DD-YY HH:MM:SS
MON-DD-YYYY HH:MM:SS
YYYY-MM-DD HH:MM:SS
YY-MM-DD HH:MM:SS
YYYY-MON-DD HH:MM:SS
YY-MON-DD HH:MM:SS

To get time in 24-hour format use HH24 like, “DD-MM-YYYY HH24:MM:SS“. Except for these formats, there are many other formats are available for the Oracle database.

Example:-

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MM:SS') 
FROM dual;

Output:-

TO_CHAR(SYSDATE,’DD-
--------------------
19-AUG-2020 06:08:33

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MM:SS') 
FROM dual;

Output:-

TO_CHAR(SYSDATE,’DD-
--------------------
19-AUG-2020 18:08:41

Oracle Getdate

Both SYSDATE and GETDATE perform identically. SYSDATE is compatible with Oracle syntax, and GETDATE is compatible with Microsoft SQL Server syntax.

Both functions don’t take any parameter and return date type value. Since GETDATE is not compatible with Oracle, so we can’t use it in the Oracle database.

SELECT GETDATE FROM DUAL;

Output:-

SELECT GETDATE FROM DUAL
*
ERROR at line 1:
ORA-00904: “GETDATE”: invalid identifier

Oracle Trunc SYSDATE

The SYSDATE returns the current date with time. But the TRUNC(SYSDATE) will return the current date start time, that is the current date at 12:00:00 night (AM). Similarly, ROUND(SYSDATE) will return the nearest date start time, that is the current date start time if time is before 12:00:00 noon (PM) or the next date start time if time is after 12:00:00 noon (PM)

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MM:SS') 
FROM dual;

Output:-

TO_CHAR(SYSDATE,’DD-
--------------------
19-AUG-2020 06:08:36

SELECT TO_CHAR(ROUND(SYSDATE), 'DD-MON-YYYY HH:MM:SS') 
FROM dual;

Output:-

TO_CHAR(ROUND(SYSDAT
--------------------
20-AUG-2020 12:08:00

SELECT TO_CHAR(TRUNC(SYSDATE), 'DD-MON-YYYY HH:MM:SS') 
FROM dual;

Output:-

TO_CHAR(TRUNC(SYSDAT
--------------------
19-AUG-2020 12:08:00

Display Consecutive Dates from Current Date using Level

Syntax to display consecutive dates from current date using level,

SELECT SYSDATE + LEVEL - 1
FROM Dual
CONNECT BY LEVEL <= N

Where N is consecutive number of days. Example:-

SELECT SYSDATE + LEVEL - 1
FROM dual
CONNECT BY Level <= 7;

Output:-

SYSDATE+L
---------
19-AUG-20
20-AUG-20
21-AUG-20
22-AUG-20
23-AUG-20
24-AUG-20
25-AUG-20
7 rows selected.

The SYSDATE and LEVEL can be used to display the consecutive number of days. The LEVEL can be used as a hierarchical structure having child nodes like 1 as root then 2 as a child then 3. The LEVEL has an initial value as 1, here SYSDATE adds up with LEVEL and acts as a multilevel structure, then could be helpful to display dates in a consecutive manner.

Inserting SYSDATE value to a Table

To insert SYSDATE to a table the column must be of DATE type.

CREATE TABLE TEST(COL1 DATE);

INSERT INTO TEST VALUES(SYSDATE);
INSERT INTO TEST VALUES(SYSDATE - 1);
INSERT INTO TEST VALUES(SYSDATE + 1);
INSERT INTO TEST VALUES(SYSDATE + 5);

COMMIT;

SELECT * FROM TEST;

Output:-

COL1
---------
19-AUG-20
18-AUG-20
20-AUG-20
24-AUG-20

Changing NLS_DATE_FORMAT

Syntax to change NLS_DATE_FORMAT for a session in Oracle database,

ALTER SESSION
SET NLS_DATE_FORMAT = 'format';

Using this we are changing NLS_DATE_FORMAT only for the current session, not for the entire database.

Example:-

ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-RR HH24:MI:SS';

SELECT SYSDATE FROM dual;

SYSDATE
------------------
19-AUG-20 10:23:39

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Also Learn,

• How to unlock Scott user in SQL Plus
• Error ORA 01031 insufficient privileges

Search