Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to insert and retrieve dates in Oracle ?
Problem:
You want to insert dates into a table and retrieve them.
Solution
Oracle, by default the uses the format DD-MON-YYYY to represent a date, where
- DD represents a two-digit day.
- MON represents first three letters of the month, e.g., FEB.
- YYYY is a four-digit year, e.g., 1968
Let us take a look at an example of adding a row to the customers table, which contains a DATE column named dob.
The following INSERT adds a row to the customers table, setting the dob column to 05-FEB-1968:
Example
INSERT INTO customers ( customer_id, first_name, last_name, dob, phone) VALUES (7, 'James', 'Bond', '31-DEC-2000', '007-007-0007');
You can also use the DATE keyword to supply a date literal to the database. The date must use the date format YYYY-MM-DD, where
- YYYY is a four-digit year.
- MM is a two-digit month from 1 to 12.
- DD is a two-digit day.
Example
INSERT INTO customers ( customer_id, first_name, last_name, dob, phone) VALUES (11, 'Roger', 'Federer', DATE '1983-10-01', '001-001-0001');
By default, the database returns dates in the format DD-MON-YY, where YY are the last two digits of the year.
CONVERTING DATETIMES USING TO_CHAR() AND TO_DATE()
The Oracle database has functions that enable you to convert a value in one data type to another.We will use the TO_CHAR() and TO_DATE() functions to convert a datetime to a string and vice versa.
TO_CHAR() to Convert a Datetime to a String
TO_CHAR(x [, format]) to convert the datetime x to a string. We can also provide an optional format for x. An example format is MONTH DD, YYYY, where
- MONTH is the full name of the month in uppercase.
- DD is the two-digit day.
- YYYY is the four-digit year.
The following query uses TO_CHAR() to convert the dob column from the customers table to a string with the format MONTH DD, YYYY
Example
SELECT customer_id, TO_CHAR(dob, 'MONTH DD, YYYY') FROM customers;
The next query gets the current date and time from the database using the SYSDATE function, then converts the date and time to a string using TO_CHAR() with the format MONTH DD, YYYY, HH24:MI:SS. The time portion of this format indicates that the hours are in 24-hour format and that the minutes and seconds are also to be included in the string.
Example
SELECT TO_CHAR(SYSDATE, 'MONTH DD, YYYY, HH24:MI:SS') FROM dual;
NOVEMBER 13, 2020, 10:04:11
TO_DATE() to Convert a String to a Datetime
We can use TO_DATE(x [, format]) to convert the x string to a datetime. We can provide an optional format string to indicate the format of x. If we omit format, the date must be in the default database format (usually DD-MON-YYYY or DD-MON-YY).
The following query uses TO_DATE() to convert the strings “31-DEC-2020” and “31-DEC-20” to the date December 31, 2020. However, the final date is displayed in the default format of DD-MON-YY.
Example
SELECT TO_DATE('31-DEC-2020'), TO_DATE('31-DEC-20') FROM dual;
Finally, a DBA can help you set the default date format in Oracle.
A DBA can change the setting of NLS_DATE_FORMAT by setting this parameter’s value in the database’s init.ora or spfile.ora file, both of which are read when the database is started. A DBA can also set NLS_DATE_FORMAT using the ALTER SYSTEM command. You can also set the NLS_DATE_FORMAT parameter for your own session using SQL*Plus, which you do by using the ALTER SESSION command
Example
ALTER SESSION SET NLS_DATE_FORMAT = 'MONTH-DD-YYYY';
17K+ Views
