DATE data type

The Oracle DATE data type is used to store date and time values. However, it's important to note that, in Oracle, the DATE data type includes both the date and the time (down to the second). This means that a DATE value stores:

• The year,
• The month,
• The day,
• The hour,
• The minute, and
• The second.

Key Points of the Oracle DATE Data Type:

1. Definition of DATE

• Oracle DATE stores both the date and time components in a single value.
• It contains information about the day, month, year, hour, minute, and second, all within a fixed length of 7 bytes.
• The format for storing the DATE data is YYYY-MM-DD HH24:MI:SS (for example, 2025-02-24 15:30:45).

2. Syntax

When defining a DATE column in a table, the syntax is simple:

CREATE TABLE table_name (

    column_name DATE

);

Oracle automatically handles the default format for the DATE data type when inserting data, but the actual format will depend on the NLS (National Language Support) settings and how you define the date format.

3. How Oracle Stores DATE

Oracle stores a DATE value as 7 bytes:

• Year: 4 bytes
• Month: 1 byte
• Day: 1 byte
• Hour: 1 byte
• Minute: 1 byte
• Second: 1 byte

This 7-byte structure is stored internally for efficient access.

4. Range of the DATE Data Type

• Earliest Date: January 1, 4712 BC (Julian calendar).
• Latest Date: December 31, 9999 AD.

In practice, these extreme limits are rarely used in real-world applications, but they show the broad range that Oracle can support with the DATE data type.

5. Default Format of DATE

By default, when you retrieve a DATE value in Oracle, it is displayed in a 'DD-MON-YYYY' format. However, Oracle allows you to change the format with the TO_DATE and TO_CHAR functions.

• Example:

SELECT SYSDATE FROM dual;

-- Output format: 24-FEB-2025 (DD-MON-YYYY by default)

If you need to specify a custom format or work with the DATE data type in different formats, you can use functions like TO_DATE, TO_CHAR, TO_TIMESTAMP, and TO_TIMESTAMP_TZ.

6. Storing and Inserting Dates

• When inserting a DATE value, you can use the TO_DATE function to convert a string into a DATE:

INSERT INTO events (event_name, event_date)

VALUES ('Annual Meeting', TO_DATE('2025-02-24 15:30:00', 'YYYY-MM-DD HH24:MI:SS'));

• If you don’t explicitly use the TO_DATE function, Oracle uses the default date format to interpret the string.

7. Working with DATE Values

The DATE data type supports various date functions and operations. Here are some of the commonly used operations and functions for DATE values:

A. Date Arithmetic

You can add or subtract days to/from a DATE column or value.

• Example: Add 10 days to a DATE value:

·        SELECT SYSDATE + 10 FROM dual;

• Example: Subtract 5 days from a DATE value:

·        SELECT SYSDATE - 5 FROM dual;

• Subtracting two DATE values returns the difference in days (including fractional days):

·        SELECT SYSDATE - TO_DATE('2025-01-01', 'YYYY-MM-DD') FROM dual;

B. Date Functions

• SYSDATE: Returns the current date and time from the system.

·        SELECT SYSDATE FROM dual;

• CURRENT_DATE: Returns the current date and time in the session's time zone.

·        SELECT CURRENT_DATE FROM dual;

• ADD_MONTHS: Adds a specified number of months to a date.

·        SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;

• LAST_DAY: Returns the last day of the month for the given date.

·        SELECT LAST_DAY(SYSDATE) FROM dual;

• NEXT_DAY: Returns the first weekday (e.g., Monday, Tuesday, etc.) after a specified date.

·        SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM dual;

• MONTHS_BETWEEN: Returns the number of months between two dates.

·        SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2025-01-01', 'YYYY-MM-DD')) FROM dual;

• EXTRACT: Extracts a part of the date (e.g., year, month, day).

·        SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;

C. Using TO_DATE and TO_CHAR

• TO_DATE: Used to convert a string into a DATE type.

SELECT TO_DATE('2025-02-24 10:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM dual;

• TO_CHAR: Converts a DATE value into a string based on the specified format.

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

8. Handling Time Zones

• The DATE data type does not store time zone information. It only stores the date and time.
• If you need to store dates and times with time zone information, you should use the TIMESTAMP WITH TIME ZONE data type.
• Oracle provides the CURRENT_TIMESTAMP and CURRENT_TIME functions for retrieving time-zone-aware date and time values.

9. Working with Time

While the DATE data type includes time information (hour, minute, second), it does not support storing fractional seconds. If you need to store precise time with fractions of a second (e.g., nanoseconds), use the TIMESTAMP or TIMESTAMP WITH TIME ZONE data type instead.

10. Comparison of DATE to TIMESTAMP

• The DATE data type stores up to seconds precision.
• The TIMESTAMP data type can store fractions of a second, making it more precise than DATE for applications requiring high precision in time.

Example:

CREATE TABLE orders (

    order_id NUMBER,

    order_date DATE,

    order_timestamp TIMESTAMP

);

11. Default Value for DATE Columns

By default, if no value is provided for a DATE column, Oracle will set it to NULL unless a DEFAULT value is explicitly specified.

• You can use SYSDATE or CURRENT_DATE as a default:

CREATE TABLE transactions (

    transaction_id NUMBER,

    transaction_date DATE DEFAULT SYSDATE

);

12. Date Formatting in SQL Queries

When querying dates in Oracle, you can use TO_CHAR to format the result of a DATE column into a specific string format.

SELECT TO_CHAR(transaction_date, 'YYYY-MM-DD HH24:MI:SS') FROM transactions;

13. Important Considerations

• Time Zones: DATE does not store time zone information. For time zone-sensitive applications, use the TIMESTAMP WITH TIME ZONE data type.
• Precision: The DATE data type stores up to seconds, so fractional seconds are not stored.
• Date Arithmetic: You can easily perform date arithmetic (addition or subtraction) and calculate the difference in days between two dates.

14. Example Use Case: Employee Record Table

CREATE TABLE employee_records (

    employee_id NUMBER,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50),

    hire_date DATE

);

 

-- Inserting data

INSERT INTO employee_records (employee_id, first_name, last_name, hire_date)

VALUES (1, 'John', 'Doe', TO_DATE('2025-02-24 09:30:00', 'YYYY-MM-DD HH24:MI:SS'));

 

-- Querying data

SELECT * FROM employee_records WHERE hire_date > TO_DATE('2025-01-01', 'YYYY-MM-DD');

Conclusion:

The Oracle DATE data type is versatile for storing both date and time information. It supports various date and time functions, allowing for easy manipulation and extraction of components. For applications that need higher precision or time zone awareness, other data types like TIMESTAMP or TIMESTAMP WITH TIME ZONE should be considered.