TO_DATE function in oracle to convert string to different formats of dates

This explains how to convert the string to date format in oracle.

There is function by the name, TO_DATE which is used to return the date from the string in any format.

Syntax: TO_DATE (str, [DateFormat], [NlsLang])

Here, str is the input string which needs to be converted to the date format, DateFormat is the format that str needs to presented in,  and NlsLang is the nls languageused to convert the str to a date. The last two parameters are optional here.

Valid Date Formats are as follows:

YEAR     Year, spelled out
YYYY     4-digit year
YYY
YY
Y         Last 3, 2, or 1 digit(s) of year.
IYY
IY
I          Last 3, 2, or 1 digit(s) of ISO year.
IYYY     4-digit year based on the ISO standard
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.
Q         Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM     Month (01-12; JAN = 01).
MON     Abbreviated name of month.
MONTH Name of month, padded with blanks to length of 9 characters.
RM     Roman numeral month (I-XII; JAN = I).
WW     Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the         year.
W         Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW     Week of year (1-52 or 1-53) based on the ISO standard.
D         Day of week (1-7).
DAY     Name of day.
DD     Day of month (1-31).
DDD     Day of year (1-366).
DY         Abbreviated name of day.
J         Julian day; the number of days since January 1, 4712 BC.
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).
FF         Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.
AM, A.M., PM, or P.M.     Meridian indicator
AD or A.D                     AD indicator
BC or B.C.                     BC indicator
TZD     Daylight savings information. For example, 'PST'
TZH     Time zone hour.
TZM     Time zone minute.
TZR     Time zone region.


Ex1: SELECT TO_DATE('01-may-10','yyyy-mm-dd') FROM dual;
This returns “5/10/2010”

Ex2: SELECT TO_DATE('may 5, 2010','MON DD, yyyy') FROM dual;
This returns “5/5/2010”