Oracle TO_DATE

This tutorial explains how to use Oracle TO_DATE function with basic syntax and examples.

TO_DATE function convert a character string to a date format.

Syntax:

TO_DATE(char[, 'format_model'], [nlsparams])

• Char : The character that will be converted to a date

• 'format_model' : 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:

Parameter	Explanation
SCC or CC	Century; server prefixes BC date with –
Years in dates YYYY or SYYYY	Year; server prefixes BC date with –
YYY or YY or Y	Last three, two, or one digits of year
Y, YYY	Year with comma in this position
IYYY, IYY, IY, I	Four-, three-, two-, or one-digit year based on the ISO standard
SYEAR or YEAR	Year spelled out; server prefixes BC 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 or brown
WW or W	Week of year or month
DDD or DD or D	Day of year, month, or week
DAY	Name of day padded with blanks to a length of nine characters
DY	Name of day; three-letter abbreviation
J	Julian day; the number of days since December 31, 4713 BC
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, AM, PM, or PM	Meridian indicator

• Nlsparams : has the same purpose in this function as in the TO_CHAR function for date conversion

Example:

Let's get some examples about Oracle TO_DATE and how to use it:

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

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

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

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;

Source :  https://www./wiki/oracle-to-date