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:
See also:
- Oracle To_Char(number) Examples
- Oracle To_Char(date) Examples
- Convert Date to Local Time Zone in Oracle