Oracle TO_DATE Function - Complete Guide

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:

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

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’.

Parameter	Explanation
SCC or CC	Century; server prefixes B.C. date with –
Years in dates YYYY or SYYYY	Year; server prefixes B.C. date with –
YYY or YY or Y	Last three, two, or one digit of the year
Y,YYY	The year with a comma in this position
IYYY, IYY, IY, I	Four-, three-, two-, or one-digit years based on the ISO standard
SYEAR or YEAR	Year spelled out; server prefixes B.C. date with –
Q	Quarter of year
MM	Month: two-digit value
MONTH	Name of month padded with blanks to length of nine characters
MON	Name of month, three-letter abbreviation
RM	Roman numeral month
WW or W	Week of year or month
DDD or DD or D	Day of the year, month, or week
DAY	Name of day padded with blanks to a length of nine characters
DY	Name of the day; three-letter abbreviation
J	Julian day; the number of days since December 31, 4713 B.C.
RRRR	Accepts a 2-digit year and returns a 4-digit year.A value between 0-49 will return a 20xx year. A value between 50-99 will return a 19xx year.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
AM, A.M., PM, or P.M.	Meridian indicator

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
oracle to_date NLS_DATE_FORMAT-min

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

Similar oracle functions Like Oracle TO_DATE
TO_CHAR : Convert number and date to string.
TO_NUMBER : Converts a value to a NUMBER type

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.

Blog

Oracle TO_DATE Function - Complete Guide

Hassan Abd Elrahman

Examples:

Example (1):

Example (2):

Join Our Newsletter. No Spam, Only the good stuff.

Related