How SYSDATE function works in PL/SQL

The PLSQL SYSDATE function will returns current system date and time on your database. There is no any parameter or argument for the SYSDATE function. The SYSDATE function returns a date value. Note that the SYSDATE function return date and time as “YYYY-MM-DD HH:MM:SS” (string) or as YYYYMMDDHHMMSS (numeric).

Syntax:

SYSDATE

Example-1:

SELECT Sysdate AS System_date
FROM Dual

Explanation:
Above example will display current date from system on database. It will execute the Syntax ‘Sysdate’ from dual, which act as dummy table and display date only in default format. In above query ‘System_date’ act as Alias Name i.e., Temporary Name of that specified column.

Output:

System_date
2019-01-05

1. Display both Date and Time –

Syntax:

SELECT TO_CHAR(Sysdate, Format_Of_Date_and_Time) AS Alias_Name
FROM Dual 

Format_Of_Date_and_Time:
DD-MM-YYYY HH:MM:SS
MM-DD-YYYY HH:MM:SS
YYYY-MM-DD HH:MM:SS
DD-MON-YYYY HH:MM:SS
MON-DD-YY HH:MM:SS
DD-MM-YYYY HH24:MM:SS (For 24hr time Format) 

Example-2:

SELECT TO_CHAR (Sysdate, 'DD-MM-YYYY HH24:MI:SS') AS System_date_time
FROM Dual

Output:

System_date_time
05-01-2019 09:45:51

Example-3:

SELECT TO_CHAR (Sysdate, 'YYYY-MON-DD HH24:MI:SS') AS System_date_time
FROM Dual

Output:

System_date_time
2019-JAN-05 09:45:51

Explanation:
In above example, it will display both date as well as time on database in specified format. TO_CHAR() Function is basically helpful to convert System date into date and time of specified format. Here ‘System_date_time’ also act as Alias Name and display both date and time in defined column.

2. Display Consecutive Dates from Current Date using Level –

Syntax:

SELECT Sysdate+Level-1
FROM Dual
CONNECT BY Level<=n 

Where n is consecutive number of days.

Example-4:

SELECT Sysdate+Level-1 AS Consecutive_dates
FROM Dual
CONNECT BY Level<=5

Explanation:
For display of consecutive number of dates, we can use SYSDATE with LEVEL. Level can be used as hierarchical structure having child nodes like 1 as root then 2 as child then 3. Level have initial value as 1, here Sysdate adds up with Level and act as multilevel structure, then could be helpful to display dates in consecutive manner.

Output:

Consecutive_dates
2019-01-05
2019-01-06
2019-01-07
2019-01-08
2019-01-09