In this tutorial, we are going to explain how to use Oracle TO_DATE function with basic syntax and many examples for better understanding.

How to convert varchar to date in oracle? Oracle TO_DATE function is used to convert a character string that is one of the data types (CHAR, VARCHAR2, NCHAR, and NVARCHAR2) into a date format.

Syntax:

TO_DATE(char, 'format_model', nls_language)

• char: The character that will be converted to a date
• ‘format_model’: a format that will be used to convert char to a date. If format_model is not specified, the format is DD-MON-YY., and It can be one or a combination of the following values:

Oracle Date Format

Using a date format mask, we can convert a character string to a particular date format if we use one or a combination of different masks like ‘DD/MM’ or ‘DD/MM/YYYY’ or ‘DD-MON’

• nls_language: it’s an expression that specifies the name of the day and month and what it looks like. it’s an optional parameter; Oracle TO_DATE function will use the default language for your session.

'NLS_DATE_LANGUAGE = American'

Oracle TO_DATE function with NLS_DATE_FORMAT :

If the client sets the NLS_* parameters — they override the server in all cases. In fact, if the client sets the NLS_LANG parameter — that causes all
NLS_* settings on the server to be ignored and the defaults for that NLS_LANG specified on the client on use. Source: Oracle

If we need to show the date in a specific format like “yyyymmdd”, we have to set NLS_DATE_FORMAT on the session level after connecting.

First, we can query the current value of NLS_DATE_FORMAT by executing this select statement :

SELECT *
FROM   V$NLS_PARAMETERS
WHERE  PARAMETER = 'NLS_DATE_FORMAT';

Result

After that, we need to set NLS_DATE_FORMAT by :

ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YYYY';

Examples:

Let’s take some examples about TO_DATE and how to use it:

Example (1):

SELECT to_date ('2019/03/01', 'yyyy/mm/dd')
FROM   dual;

Result

Example (2):

SELECT TO_DATE('030119', 'MMDDYY')
FROM   dual;

Result

Example (3):

SELECT TO_DATE('20190301', 'yyyymmdd')
FROM   dual;

Result

Example (4):

SELECT TO_DATE('2019/03/01 18:00:59', 'YYYY/MM/DD HH24:MI:SS'), TO_DATE('2019/02/05 09:19:59', 'YYYY/MM/DD HH:MI:SS')
FROM   dual;

Result

Example (5) :

SELECT TO_DATE ('January 15, 2020', 'Month dd, RRRR', 'NLS_DATE_LANGUAGE = American')
FROM   DUAL;

Result

Similar oracle functions Like Oracle TO_DATE:

TO_CHAR – Convert number and date to string.
TO_NUMBER – Converts a value to a NUMBER type
CAST – used for many types, including dates

In this tutorial, you have learned how to use the Oracle TO_DATE function to convert a character string to a date format by giving 4+ different examples for better understanding.

Hopefully, it was clear and concise.