A Comprehensive Note to Using Oracle SYSDATE

In Oracle, the SYSDATE function plays a crucial role in retrieving the current date and time from the system. This powerful feature enables developers and database administrators to perform various operations related to date and time. In this comprehensive guide, we will explore different ways to utilize the SYSDATE function and retrieve date, time, or both from the system. Let's dive into the details.

Retrieving the Date from the System:

To obtain the current date from the system, you can use the following SQL query:

SELECT SYSDATE FROM DUAL;

Formatting the Date:

If you prefer a specific date format, you can apply the TO_CHAR function to the SYSDATE result. For example, to display the date in the 'YYYY MM DD' format, you can use the query below:

SELECT TO_CHAR(SYSDATE,'YYYY MM DD') FROM DUAL;

Retrieving the Time from the System: 

Similarly, to retrieve the current time from the system, you can use the TO_CHAR function with the appropriate format mask. The following query displays the time in the 'HH MI SS' format:

SELECT TO_CHAR(SYSDATE,'HH MI SS') FROM DUAL;

Adding a Heading to the Date:

To enhance the output, you can include a heading for the date. The query below adds a heading called "TODAY_DATE" to the formatted date:

SELECT TO_CHAR(SYSDATE,'YYYY MM DD') AS TODAY_DATE FROM DUAL;

Adding a Heading to the Time:

Similarly, to add a heading for the time, use the AS keyword in the query. The example below includes a heading called "NOW_TIME" for the formatted time:

SELECT TO_CHAR(SYSDATE,'HH MI SS') AS NOW_TIME FROM DUAL;

Retrieving Time in 24-Hour Format:

If you prefer the time to be displayed in a 24-hour format, use the 'HH24' format mask instead. The query below retrieves the current time in a 24-hour format:

SELECT TO_CHAR(SYSDATE,'HH24 MI SS') AS NOW_TIME FROM DUAL;

Retrieving Time in 12-Hour Format:

On the other hand, if you want the time to be displayed in a 12-hour format, use the 'HH' format mask. The query below demonstrates this:

SELECT TO_CHAR(SYSDATE,'HH MI SS') AS NOW_TIME FROM DUAL;

Retrieving Date and Time in 12-Hour Format: 

To retrieve both the date and time in a 12-hour format, combine the appropriate format masks. The following query retrieves the date and time in the 'YYYY MM DD HH MI SS' format:

SELECT TO_CHAR(SYSDATE,'YYYY MM DD HH24 MI SS') AS DAT_TIME FROM DUAL;

Utilizing the SYSDATE function in Oracle provides immense flexibility in retrieving the current date and time from the system. By employing the TO_CHAR function with different format masks, you can customize the output to meet your specific requirements. Experiment with these techniques and incorporate them into your Oracle queries to make the most out of SYSDATE.