Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle TO_DATE Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: TO_DATE

Definition:
The Oracle TO_DATE function will convert either a character string or an expression into a date value.

Example Syntax
These all show valid date formats for "February 16th, 2009":

to_date('16-Feb-09', 'DD-Mon-YY')
to_date('02/16/09', 'MM/DD/YY')
to_date('021609', 'MMDDYY')
to_date('16-Feb-09', 'DD-Mon-YY HH:MI:SS')
to_date('Feb/16/09', 'Mon/DD/YY HH:MI:SS')
to_date('February.16.2009', 'Month.DD.YYYY HH:MI:SS')


The 'format' string must be a valid DATE format: YYYY=year, MM=month, DD=Day, HH=Hour, Mi=Minute
If no format is specified Oracle will assume the default date format has been supplied in char.

Note that the return value of TO_DATE can be influenced by setting the NLS_DATE_LANGUAGE and NLS_CALENDAR parameters.


TO_DATE Format Masks
FormatReturns
DDay of the week
DDDay of the month
DDDNumerical day of the year, 1 ~ 365 (366 for Leap years)
DAYFull textual representation of the day, i.e. "Monday", "Tuesday", "Wednesday"
DYDay in three letters, i.e. "MON", "TUE", "FRI"
WWeek of the month
WWWeek of the year
MMMonth in two digits, i.e. 01 = Jan, 02 = Feb,...12 = -Dec
MONMonth in three characters, i.e. "Jan", "Feb", "Apr"
MONTHFull textual representation of the Month, i.e. "January", "February", "April"
RMMonth in Roman Characters (I-XII, I-Jan, II-Feb, ... XII-Dec)
QQuarter of the Month
YYLast two digits of the year.
YYYYFull year
YEARYear in words like "Nineteen Eighty Seven"
HHHours in 12 hour format
HH12Hours in 12 hour format
HH24Hours in 24 hour format ("military time")
MIMinutes
SSSeconds
FFFractional Seconds
SSSSSMilliseconds
JJulian Day i.e Days since 1st-Jan-4712BC to till-date
RRIf the year is less than 50 then Oracle considers the year as a 21st century date. If the year is greater than 50 then Oracle considers the year to be in the 20th century.



Related Links:

Related Code Snippets:
  • Date Functions: To_Date - The to_date function converts a string to a date.
  • TO_DATE - TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE...
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 122 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?