Specifying a Datetime Format for TO_DATE() function : TO_DATE « Date Timestamp Functions « Oracle PL/SQL Tutorial

Home
Oracle PL/SQL Tutorial
1.Introduction
2.Query Select
3.Set
4.Insert Update Delete
5.Sequences
6.Table
7.Table Joins
8.View
9.Index
10.SQL Data Types
11.Character String Functions
12.Aggregate Functions
13.Date Timestamp Functions
14.Numerical Math Functions
15.Conversion Functions
16.Analytical Functions
17.Miscellaneous Functions
18.Regular Expressions Functions
19.Statistical Functions
20.Linear Regression Functions
21.PL SQL Data Types
22.PL SQL Statements
23.PL SQL Operators
24.PL SQL Programming
25.Cursor
26.Collections
27.Function Procedure Packages
28.Trigger
29.SQL PLUS Session Environment
30.System Tables Data Dictionary
31.System Packages
32.Object Oriented
33.XML
34.Large Objects
35.Transaction
36.User Privilege
Oracle PL/SQL Tutorial » Date Timestamp Functions » TO_DATE 




AspectParameter DescriptionExample
CenturyCCTwo-digit century.
21CenturySCC
Two-digit century with a negative sign (C) for B.C.-10Quarter
QOne-digit quarter of the year.1
YearYYYYAll four digits of the year.
2006YearIYYY
All four digits of the ISO year.2006Year
RRRRAll four digits of the rounded year, which depends on the current year.2006
YearSYYYYAll four digits of the year with a negative sign (C) for B.C.
C1001YearY,YYY
All four digits of the year with a comma.2,006Year
YYYLast three digits of the year.006
YearIYYLast three digits of the ISO year.
006YearYY
Last two digits of the year.06Year
IYLast two digits of the ISO year. 06Year
RRLast two digits of the rounded year, which depends on the current year.06
YearYLast digit of the year.
6YearI
Last digit of the ISO year.6Year
YEARName of the year in uppercase.TWO THOUSAND-SIX
YearYearName of the year with the first letter in uppercase.
Two Thousand-SixMonthMM
Two-digit month of the year.01Month
MONTHFull name of the month in uppercase, right-padded with spaces to a total length of nine characters.JANUARY
MonthMonthFull name of the month with first letter in uppercase, right-padded with spaces to a total length of nine characters.
JanuaryMonthMON
First three letters of the name of the month in uppercase.JANMonth
MonFirst three letters of the name of the month with the first letter in uppercase.Jan
MonthRMRoman numeral month.
The Roman numeral month for the fourth month (April) is IV.WeekWW
Two-digit week of the year.02Week
IWTwo-digit ISO week of the year.02
WeekWOne-digit week of the month.
2DayDDD
Three-digit day of the year.103Day
DDTwo-digit day of the month.31
DayDOne-digit day of the week.
5DayDAY
Full name of the day in uppercase.SATURDAYDay
DayFull name of the day with the first letter in uppercase.Saturday
DayDYFirst three letters of the name of the day in uppercase.
SATDayDy
First three letters of the name of the day with the first letter in uppercase.SatDay
JJulian day-the number of days that have passed since January 1, 4713 B.C.2439892
HourHH24Two-digit hour in 24-hour format.
23HourHH
Two-digit hour in 12-hour format.11Minute
MITwo-digit minute.57
SecondSSTwo-digit second.
45SecondFF[1..9]
Fractional seconds with an optional number of digits to the right of the decimal point. Only applies timestamps,When dealing with 0.123456789 seconds, FF3 would round to 0.123.SecondSSSSS
Number of seconds past 12 a.m.46748Second
MSMillisecond (millionths of a second).100
SecondCSCentisecond (hundredths of a second).
10Separators-/,.;: "text"
Characters that allow you to separate the aspects of a date and time. You can supply freeform text in quotes as a separator.When dealing with the date December 13, 1969, DD-MM-YYYY would produce 12-13-1969 and DD/MM/YYYY would produce 12/13/1969Suffixes
AM or PMAM or PM as appropriate.AM
SuffixesA.M. or P.M.A.M. or P.M. as appropriate.
P.M.SuffixesAD or BC
AD or BC as appropriate.ADSuffixes
A.D. or B.C.A.D. or B.C. as appropriate.B.C.
SuffixesTHSuffix to a number. You can make the suffix uppercase by specifying the numeric format in uppercase and vice versa for lowercase.When dealing with a day number of 28, ddTH would produce 28th and DDTH would produce 28TH
SuffixesSPNumber is spelled out.When dealing with a day number of 28, DDSP would produce TWENTY-EIGHT and ddSP would produce twenty-eight
SuffixesSPTHCombination of TH and SP.When dealing with a day number of 28, DDSPTH would produce TWENTY-EIGHTH and ddSPTH would produce twenty-eighth
EraEEFull era name for Japanese Imperial, ROC Official, and Thai Buddha calendars.
No exampleEraE
Abbreviated era name.No exampleTime zones
TZHTime zone hour.12
Time zonesTZMTime zone minute.
30Time zonesTZR
Time zone region.PSTTime zones
TZDTime zone with daylight savings information.No example


Quote from:

Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)

# Paperback: 608 pages

# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)

# Language: English

# ISBN-10: 0072229810

# ISBN-13: 978-0072229813















13.21.TO_DATE
13.21.1.Specifying a Datetime Format for TO_DATE() function
13.21.2.Using the YY Format
13.21.3.Using the RR Format
13.21.4.TO_CHAR(TO_DATE('04-JUL-15', 'DD-MON-RR'), 'DD-MON-YYYY')
13.21.5.TO_DATE function to convert from characters to dates explicitly
13.21.6.TO_DATE(x[, format]) converts the x string to a datetime
13.21.7.TO_DATE() converts the strings 04-JUL-2006 to the date July 4, 2006
13.21.8.Specifying Times
13.21.9.TO_DATE() with INSERT statement
13.21.10.DBMS_OUTPUT.PUT_LINE(TO_DATE ('1/1'));
13.21.11.DBMS_OUTPUT.PUT_LINE(TO_DATE ('6/1996'));
13.21.12.DBMS_OUTPUT.PUT_LINE(TO_DATE ('12-APR-09'));
13.21.13.DBMS_OUTPUT.PUT_LINE(TO_DATE ('19991205'));
13.21.14.DBMS_OUTPUT.PUT_LINE(TO_DATE ('1/1/1'))
13.21.15.Birthday before 1940
13.21.16.Catch exception from to_date function
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.