Home » SQL » Oracle TO_DATE Function Examples

Oracle TO_DATE Function Examples

By

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.

Description

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

Syntax

TO_DATE (char, format, [nlsparam])

Oracle TO_DATE Format Specifiers

Format Model Description
CC, SCC Century (S prefixes BC dates with a minus sign)
YYYY, SYYYY Year (S prefixes BC dates with a minus sign)
IYYY Year based on ISO standard
YYY, YY, Y Last three, two or one digits of the year
IYY, IY, I Last 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, SYEAR Year spelled out (S prefixes BC dates with a minus sign)
RR Last two digits of year in another century (allows for year 2000)
BC, AD BC or AD indicator
B.C., A.D. BC or AD indicators with periods
Q Numeric quarter of the year (1-4 with Jan-Mar=1)
MM 2 number month (Jan = 01)
RM Roman numeral month
MONTH Name of month spelled out (upper case - month is upper case)
MON abbreviated name of month (upper case - month is upper case)
WW Week of the year (1-53)
IW Week of the year (1-52, 1-53) based on ISO standard
W Week of month (1-5)
DDD day of year (1-366) (Don't forget leap years)
DD day of month (1-31)
D day of week (1-7)
DAY Name of day (upper case, day is upper case)
DY Abbreviated name of day
J Julian day (Number of days since January 1, 4712 BC)
AM,PM Meridian indicator
A.M., P.M. Meridian indicator with periods.
HH, HH12 Hour of day (0-12)
HH24 Use 24 hour clock for hours (1-24)
MI Minute (0-59)
SS Second (0-60)
SSSSS (five S's) seconds past midnight. (0-86399)
None Date 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: