What Is the Data Type for Datetime in Oracle?

What Is the Data Type for Datetime in Oracle?

In Oracle, the data type used to store datetime values is called DATE. The DATE data type is used to represent a specific point in time, including both date and time components. It can store values ranging from January 1st, 4712 BC to December 31st, 9999 AD.

Date Format

The default format for displaying dates in Oracle is DD-MON-YY, where:

• DD represents the day of the month (e.g., 01, 02, .., 31).
• MON represents the abbreviated name of the month (e., JAN, FEB, ., DEC).
• YY represents the last two digits of the year (e., 21 for the year 2021).

For example, if you have a date value of ’01-JAN-21′, it represents January 1st, 2021.

Date and Time Functions

In addition to storing datetime values, Oracle provides a rich set of functions to manipulate and extract information from them. Some commonly used datetime functions include:

• SYSDATE(): Returns the current system date and time.
• TO_CHAR(date_value, format): Converts a date value into a string using the specified format.
• TO_DATE(string_value, format): Converts a string into a date value using the specified format.
• EXTRACT(unit FROM date_value): Extracts a specific component (e., year, month, day) from a date value.

Example Usage

Let’s take a look at some examples:

SELECT SYSDATE FROM dual;
-- Returns the current system date and time.

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') FROM dual;
-- Converts the current system date and time into a formatted string.

SELECT TO_DATE('01-JAN-2021', 'DD-MON-YYYY') FROM dual;
-- Converts the string '01-JAN-2021' into a date value.

SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;
-- Extracts the year component from the current system date.

Conclusion

The DATE data type in Oracle is used to store datetime values. It allows you to represent specific points in time and provides various functions for manipulating and extracting information from these values. Understanding how to work with dates in Oracle is essential for developing applications that deal with time-sensitive data.