Oracle / PLSQL: TO_DATE Function

This article is written about how to use the Oracle/PLSQL TO_DATE function with syntax and examples.

Description

The Oracle/PLSQL TO_DATE function converts a string to a date.

Syntax

The syntax for the TO_DATE function in Oracle/PLSQL is:

TO_DATE( string1 [, format_mask] [, nls_language] )

Parameters or Arguments

string1 The string that will be converted to a date. format_mask Optional. This is the format that will be used to convert string1 to a date. It can be one or a aggregate of the following values: Parameter Explanation 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 price between 0-49 will return a 20xx year. A cost 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 begins on the first day of the yr and continues to the seventh day of the year. W Week of month (1-5) the place week 1 begins on the first day of the month and ends on the seventh. IW Week of yr (1-52 or 1-53) based totally on the ISO standard. D Day of week (1-7). DAY Name of day. DD Day of month (1-31). DDD Day of 12 months (1-366). DY Abbreviated title of day. J Julian day; the variety of days when you consider that 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 previous midnight (0-86399). 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 quarter hour. TZM Time area minute. TZR Time area region. nls_language Optional. This is the nls language used to convert string1 to a date.

Returns

The TO_DATE function returns a date value.

Applies To

The TO_DATE function can be used in the following variations of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example

Let’s seem to be at some Oracle TO_DATE characteristic examples and discover how to use the TO_DATE characteristic in Oracle/PLSQL.

For example:

TO_DATE('2003/07/09', 'yyyy/mm/dd')
Result: date value of July 9, 2003

TO_DATE('070903', 'MMDDYY')
Result: date value of July 9, 2003

TO_DATE('20020315', 'yyyymmdd')
Result: date value of Mar 15, 2002

You may want to use the TO_DATE characteristic with the dual desk as follows: