Date Datatype in Oracle Database

by
use date datatype in sql developer tool

In this Oracle tutorial, we will learn about the date datatype in the oracle database. Also, we will illustrate how to use it to create date columns for a table in oracle.

Introduction to Oracle Date datatype

Date and time values are stored in Oracle 21c using the DATE data type. Using this widely used data type, you can record various dates, including years, months, days, hours, minutes, and seconds.

The year, month, day, hour, minute, and second are all saved as part of a DATE value in Oracle in a 7-byte binary format. The DATE data type accepts dates between January 1st, 4712 BC, and December 31st, 4712 AD.

Oracle Date format

The NLS DATE FORMAT parameter controls the default date format for input and output, DD-MON-YY.

Oracle Date datatype Syntax

The syntax for declaring a column with the DATE data type in Oracle 21c is as follows:

column_name DATE;

Also, check: How to copy a table in Oracle

Oracle Date datatype Example

After discussing the syntax, we will now discuss the example of how to use the Date datatype in oracle 21c.

Now we will create a table ‘transaction’ with a DATE column named “transaction_date”.

CREATE TABLE transactions (
   transaction_id NUMBER,
   transaction_date DATE,
   transaction_amount NUMBER
);

To insert a date value into the “transaction_date” column, you can use the following statement.

INSERT INTO transactions (transaction_id, transaction_date, transaction_amount)
VALUES (1, TO_DATE('2021-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS'), 100);
inserted values in transcation table
inserted values in the transaction table

Here, the TO_DATE function is used to convert the string value ‘2021-01-01 12:00:00’ into a DATE value that can be inserted into the “transaction_date” column.

Note: The DATE data type in Oracle supports a wide range of date and time formats, so you can use a different format string with the TO_DATE function if needed. For example, to use another format string such as ‘YYYY-MM-DD’, you could use the following statement.

INSERT INTO transactions (transaction_id, transaction_date, transaction_amount)
VALUES (1, TO_DATE('2021-01-01', 'YYYY-MM-DD'), 100);
insert values into transcation table
insert values into the transaction table

Read: How to create a user in Oracle

Example

Now, we will create the table and insert the column name as well as provide the datatype in this example, we utilize the date datatype with size(20) in SQL developer.

use date datatype in sql developer tool
use date datatype in an SQL developer tool

Now we will insert the values into an employee table

INSERT INTO employees (emp_id, emp_name, joining_date)
VALUES (1,'John', TO_DATE('2019-12-02 12:00:00', 'YYYY-MM-DD HH24:MI:SS'));
insert values into employees table
insert values into employees table

Also, check: Change Database Name in Oracle

Conclusion

So, In this Oracle tutorial, we understood how to define and use the Date Datatype in Oracle Database. And we have also covered a few sample examples related to it.

Also, take a look at some more Oracle tutorials.