Oracle TO_DATE Function Examples

In this Oracle tutorial, examples are given to how to convert a string to date in Oracle using TO_Date function. Also, providing TO_DATE format specifier details for more understanding.


TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype.


TO_DATE (char, format, [nlsparam])

Oracle TO_DATE Format Specifiers

Format ModelDescription
CC, SCCCentury (S prefixes BC dates with a minus sign)
YYYY, SYYYYYear (S prefixes BC dates with a minus sign)
IYYYYear based on ISO standard
YYY, YY, YLast three, two or one digits of the year
IYY, IY, ILast three, two or one digits of the ISO year
Y,YYY(Four Y's with comma) put a comma in the year (1,995)
YEAR, SYEARYear spelled out (S prefixes BC dates with a minus sign)
RRLast two digits of year in another century (allows for year 2000)
BC, ADBC or AD indicator
B.C., A.D.BC or AD indicators with periods
QNumeric quarter of the year (1-4 with Jan-Mar=1)
MM2 number month (Jan = 01)
RMRoman numeral month
MONTHName of month spelled out (upper case - month is upper case)
MONabbreviated name of month (upper case - month is upper case)
WWWeek of the year (1-53)
IWWeek of the year (1-52, 1-53) based on ISO standard
WWeek of month (1-5)
DDDday of year (1-366) (Don't forget leap years)
DDday of month (1-31)
Dday of week (1-7)
DAYName of day (upper case, day is upper case)
DYAbbreviated name of day
JJulian day (Number of days since January 1, 4712 BC)
AM,PMMeridian indicator
A.M., P.M.Meridian indicator with periods.
HH, HH12Hour of day (0-12)
HH24Use 24 hour clock for hours (1-24)
MIMinute (0-59)
SSSecond (0-60)
SSSSS(five S's) seconds past midnight. (0-86399)
NoneDate must be in the format 'DD-MON-YY';

Oracle TO_DATE Function Examples

1. Example to convert a string containing YYYYMMDD format into Date data type.



Note: String format should match with the format you are specifying to convert, else it will give the error.


2. Convert a long string containing date-time information into a date-time data type.

'May 15, 2018, 11:00 A.M.',
'Month dd, YYYY, HH:MI A.M.',


15/05/2018 11:00:00

3. Convert a string to a date using the Spanish language.

SELECT TO_DATE ('Abril 12 2018',
'Month DD YYYY',




Oracle TO_DATE

See also:

Written by Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of and a question and answer forum for developers. You can connect with me on Facebook and Twitter.

Leave a Reply

Your email address will not be published. Required fields are marked *