TO_DATE() function converts a date literal to a
The following illustrates the syntax of the Oracle
TO_DATE (string, format, nls_language)
TO_DATE() function accepts three arguments:
is the date and time format for the
format argument is optional. If you omit the format, the string must be in the standard date format which is
Noted that if
J, which is for Julian, then the
string must be an integer.
For the detailed information on how to construct the
format, check it out the Oracle date format.
is an expression that specifies the language for day and month names in the
nls_language argument has the following form:
NLS_DATE_LANGUAGE = language
ls_language argument is optional. If you omit it, the
TO_DATE() function will use the default language for your session.
TO_DATE() function returns a
DATE value which is corresponding to the input string.
TO_DATE() function example
To convert a string to date, you use the Oracle date format elements e.g., YYYY for the 4-digit year, MM for the 2-digit month, etc.
Suppose, you have the following date literal:
'5 Jan 2017'
To convert this string to a
DATE value, first, you must construct the date format model for it. In this case,
5 is represented by
Jan is represented by
2017 is represented by
YYYY that results in the following format:
'DD Mon YYY'
Second, you pass both arguments, date literal and format model, to the
TO_DATE() function as shown below:
SELECT TO_DATE( '5 Jan 2017', 'DD MON YYYY' ) FROM dual;
The following illustrates the output:
If you fail to construct the format, the
TO_DATE() function will issue a respective error.
For example, if you miss one character Y in the date format model above:
SELECT TO_DATE( '5 Jan 2017', 'DD MON YYY' ) FROM dual;
TO_DATE() function issued the following error:
ORA-01830: date format picture ends before converting entire input string
TO_DATE() function to insert data into a table
The following statement creates a table named
CREATE TABLE members ( member_id NUMBER GENERATED BY DEFAULT AS IDENTITY, first_name VARCHAR2 ( 50 ) NOT NULL, last_name VARCHAR2 ( 50 ) NOT NULL, joined_date DATE NOT NULL, PRIMARY KEY ( member_id ) );
The following statement inserts a new row into the
INSERT INTO members(first_name, last_name, joined_date) VALUES('Laureen','Davidson', TO_DATE('Feb 01 2017','Mon DD YYYY'));
In this example, because
Feb 01 2017 is not Oracle standard date format, you have to use the
TO_DATE() function to convert it to a
DATE value before storing in the table.
Here is another example of inserting a date value a DATE column using the
TO_DATE() function for formatting.
INSERT INTO members(first_name, last_name, joined_date) VALUES('Thomas','Phelps', TO_DATE('15 March 2017','DD Month YYYY'));
You can check whether the data was actually inserted by using the following query:
SELECT * FROM members;
In this tutorial, you have learned how to use the
TO_DATE() function to convert a string into a