Introduction to Oracle DATE Data Type

In Oracle database, DATE is a data type used to store date and time information. This type supports dates and times from January 1, 4712 BC to December 31, 9999 AD.

Syntax

The syntax for DATE data type is as follows:

DATE

Use Cases

DATE data type is commonly used for storing date and time information, such as in applications like orders, calendars, logs, etc. It can store information like year, month, day, hour, minute, and second.

Examples

Here are two examples of using DATE data type.

Example 1

Create a table named employee that contains employee name, birthdate, and hiredate.

CREATE TABLE employee (
  name VARCHAR2(50),
  birthdate DATE,
  hiredate DATE
);

Insert some data into the employee table.

INSERT INTO employee (name, birthdate, hiredate)
VALUES ('John', TO_DATE('1990-01-01', 'YYYY-MM-DD'), TO_DATE('2018-01-01', 'YYYY-MM-DD'));

INSERT INTO employee (name, birthdate, hiredate)
VALUES ('Mary', TO_DATE('1988-06-15', 'YYYY-MM-DD'), TO_DATE('2017-07-01', 'YYYY-MM-DD'));

Retrieve data from the employee table.

SELECT * FROM employee;

Output:

NAME  BIRTHDATE   HIREDATE
----  ----------  ----------
John  01-JAN-90   01-JAN-18
Mary  15-JUN-88   01-JUL-17

Example 2

Using DATE data type in a WHERE clause.

Create a table named orders that contains order number, customer name, and order date.

CREATE TABLE orders (
  order_number NUMBER,
  customer_name VARCHAR2(50),
  order_date DATE
);

Insert some data into the orders table.

INSERT INTO orders (order_number, customer_name, order_date)
VALUES (1, 'John', TO_DATE('2023-04-01', 'YYYY-MM-DD'));

INSERT INTO orders (order_number, customer_name, order_date)
VALUES (2, 'Mary', TO_DATE('2023-03-15', 'YYYY-MM-DD'));

Retrieve data from the orders table where the order date is after a specified date using a WHERE clause.

SELECT * FROM orders WHERE order_date > TO_DATE('2023-03-01', 'YYYY-MM-DD');