Wednesday, October 27, 2010

PL/SQL Date function - to_date()

In Oracle/PLSQL, the to_date function converts a string to a date.

The syntax for the to_date function is:

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

string1 is the string that will be converted to a date.

format_mask is optional. This is the format that will be used to convert string1 to a date.

nls_language is optional. This is the nls language used to convert string1 to a date.

The following is a list of options for the format_mask parameter. These parameters can be used in many combinations.












































































































































ParameterExplanation
YEARYear, spelled out
YYYY4-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.
IYYY4-digit year based on the ISO standard
RRRRAccepts a 2-digit year and returns a 4-digit year.

A value between 0-49 will return a 20xx year.

A value between 50-99 will return a 19xx year.
QQuarter of year (1, 2, 3, 4; JAN-MAR = 1).
MMMonth (01-12; JAN = 01).
MONAbbreviated name of month.
MONTHName of month, padded with blanks to length of 9 characters.
RMRoman numeral month (I-XII; JAN = I).
WWWeek of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
WWeek of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IWWeek of year (1-52 or 1-53) based on the ISO standard.
DDay of week (1-7).
DAYName of day.
DDDay of month (1-31).
DDDDay of year (1-366).
DYAbbreviated name of day.
JJulian day; the number of days since January 1, 4712 BC.
HHHour of day (1-12).
HH12Hour of day (1-12).
HH24Hour of day (0-23).
MIMinute (0-59).
SSSecond (0-59).
SSSSSSeconds past midnight (0-86399).
FFFractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.
AM, A.M., PM, or P.M.Meridian indicator
AD or A.DAD indicator
BC or B.C.BC indicator
TZDDaylight savings information. For example, 'PST'
TZHTime zone hour.
TZMTime zone minute.
TZRTime zone region.


Applies To:

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


For example:

to_date('2003/07/09', 'yyyy/mm/dd') would return a date value of July 9, 2003.
to_date('070903', 'MMDDYY') would return a date value of July 9, 2003.
to_date('20020315', 'yyyymmdd') would return a date value of Mar 15, 2002.

No comments:

Post a Comment