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:
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);
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);
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.
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'));
Also, check: Change Database Name in Oracle
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.
- Char Datatype in Oracle Database
- Oracle Varchar2 Datatype
- Nchar Datatype in Oracle Database
- NVARCHAR2 Datatype in Oracle Database
- Float Datatype in Oracle Database
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.