How to Retrieve Date and Time Data in Oracle

Oracle Database provides powerful features for retrieving the current date and time and manipulating datetime data. This article explains in detail how to retrieve datetime data, including customization methods and important points to note.

1. Retrieving the Current Date and Time

The following are the main methods for retrieving the current date and time in Oracle Database.

SYSDATE

The SYSDATE function returns the current date and time of the database server.

By default, only the date is displayed, but you can include the time by using TO_CHAR.

-- Default SYSDATE
SELECT SYSDATE FROM DUAL;

-- Display in a format including time
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date FROM DUAL;

Example Result:

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
07-NOV-25

SQL> SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date FROM DUAL;

FORMATTED_DATE
-------------------
2025-11-07 23:42:38

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP returns the current date and time, including time zone information.

It offers high precision and can retrieve time down to the nanosecond.

SELECT CURRENT_TIMESTAMP FROM DUAL;

Example Result:

SQL> SELECT CURRENT_TIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP
-----------------------------------------------------
07-NOV-25 11.43.40.227694 PM -05:00

2. Differences in Datetime Data Types

Oracle provides the following datetime data types.

3. Retrieving with a Specified Datetime Format

To display the date and time in a specific format, use the TO_CHAR function.

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

Main Format Models

4. Changing the Format at the Session Level

You can temporarily change the default datetime format using the ALTER SESSION command.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT SYSDATE FROM DUAL;

Example Result:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
-------------------
2025-11-07 23:44:20

Note: This setting will be reset after the session ends.

5. Retrieving Datetime Considering Time Zone

In a global environment, handling time zones is crucial.

Checking Database and Session Time Zones

SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;

Example Result:

SQL> SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;

DBTIME SESSIONTIMEZONE
------ -----------------------------------------------
+00:00 -05:00

Current Datetime with Time Zone Information

SELECT SYSTIMESTAMP FROM DUAL;

Example Result:

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
07-NOV-25 11.46.27.220646 PM -05:00

6. Datetime Calculation and Comparison

Calculating the Difference in Days

To calculate the difference between two dates, simply subtract them.

SELECT SYSDATE - TO_DATE('2024-12-20', 'YYYY-MM-DD') AS days_difference FROM DUAL;

Example Result:

SQL> SELECT SYSDATE - TO_DATE('2024-12-20', 'YYYY-MM-DD') AS days_difference FROM DUAL;

DAYS_DIFFERENCE
---------------
     322.991262

Comparing Datetimes

It is also possible to compare dates conditionally and branch the result.

SELECT CASE
         WHEN SYSDATE > TO_DATE('2024-12-20', 'YYYY-MM-DD') THEN 'After'
         ELSE 'Before'
       END AS comparison_result FROM DUAL;

Example Result:

SQL> SELECT CASE
  2           WHEN SYSDATE > TO_DATE('2024-12-20', 'YYYY-MM-DD') THEN 'After'
  3           ELSE 'Before'
  4         END AS comparison_result FROM DUAL;

COMPAR
------
After

7. Practice Exercises

Try the following queries.

1. Write SQL to display the current date and time in ‘YYYY/MM/DD HH:MI AM’ format.
2. Retrieve the current date and time, including time zone information.
3. Calculate the difference between the current date and the date one week from now.

1. Display the current date and time in 'YYYY/MM/DD HH:MI AM' format We convert SYSDATE (the current date and time) to the specified format using the TO_CHAR function. The key is to use the 12-hour format (HH12) and the AM/PM format specifiers.

SQL> SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH12:MI AM') FROM DUAL;

TO_CHAR(SYSDATE,'YY
-------------------
2025/11/07 11:34 PM


2. Retrieve the current date and time including time zone information SYSDATE is a DATE type and does not have time zone information. If you need time zone information, use SYSTIMESTAMP, which returns a TIMESTAMP WITH TIME ZONE type.

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
------------------------------------------------
07-NOV-25 11.36.40.733640 PM -05:00


3. Difference between the current date and the date one week later In Oracle, you can add or subtract numbers from the DATE data type, where 1 represents "1 day." Therefore, one week from now can be expressed as SYSDATE + 7.

If you calculate the difference between these two dates, the result is, naturally, 7 days.

SQL> SELECT (SYSDATE + 7) - SYSDATE FROM DUAL;

(SYSDATE+7)-SYSDATE
-------------------
                  7
  

[reference]
Oracle Database SQL Language Reference, 19c

Oracle SELECT文の基本を解説

SELECT文はOracleデータベースを操作する上で最も基本的かつ重要なSQL文です。この文を使用することで、テーブルから必要な情報を抽出したり、条件を指定してデータを絞り込むことができます。本記事では、SELECT文の構文、基本例、応用...

oracle-master00000.com