Press "Enter" to skip to content

Oracle TO_DATE Function Examples

In this 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.

Description

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

Syntax

TO_DATE (char, format, [nlsparam])

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.

SELECT TO_DATE ('20180515', 'YYYYMMDD') FROM DUAL;

Output

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

15/05/2018

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

SELECT TO_DATE(
'May 15, 2018, 11:00 A.M.',
'Month dd, YYYY, HH:MI A.M.',
'NLS_DATE_LANGUAGE = American')
FROM DUAL;

Output

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',
'NLS_DATE_LANGUAGE=Spanish')
FROM DUAL;

Output

12/04/2018

Reference:

Oracle TO_DATE

See also: