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:



filter_none

edit
close

play_arrow

link
brightness_4
code

SELECT Sysdate AS System_date
FROM Dual

chevron_right


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:

filter_none

edit
close

play_arrow

link
brightness_4
code

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

chevron_right


Output:

System_date_time
05-01-2019 09:45:51

Example-3:

filter_none

edit
close

play_arrow

link
brightness_4
code

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

chevron_right


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:

filter_none

edit
close

play_arrow

link
brightness_4
code

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

chevron_right


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


My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.