Datetime Formatting Parameters for TO_CHAR() function : Date Format « SQL Data Types « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. SQL Data Types
  3. Date Format
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




10.7.Date Format
10.7.1.Insert Date value with default format
10.7.2.By default, the database outputs dates in the format DD-MON-YY
10.7.3.Datetime Formatting Parameters for TO_CHAR() function
10.7.4.SELECT TO_CHAR(ADD_MONTHS(TO_DATE('01-JAN-2005 19:15:26','DD-MON-YYYY HH24:MI:SS'), 2), 'DD-MON-YYYY HH24:MI:SS') FROM dual;
10.7.5.CC: Two-digit century
10.7.6.SCC: Two-digit century with a negative sign (-) for B.C.
10.7.7.Q: One-digit quarter of the year
10.7.8.YYYY: All four digits of the year
10.7.9.IYYY: All four digits of the ISO year
10.7.10.RRRR: All four digits of the rounded year, which depends on the current year
10.7.11.SYYYY: All four digits of the year with a negative sign (-) for B.C.
10.7.12.Y,YYY: All four digits of the year with a comma
10.7.13.YYY: Last three digits of the year
10.7.14.IYY: Last three digits of the ISO year
10.7.15.YY: Last two digits of the year
10.7.16.IY: Last two digits of the ISO year
10.7.17.RR: Last two digits of the rounded year, which depends on the current year
10.7.18.Y: Last digit of the year
10.7.19.I: Last digit of the ISO year
10.7.20.YEAR: Name of the year in uppercase
10.7.21.Year: Name of the year with the first letter in uppercase
10.7.22.MM: Two-digit month of the year
10.7.23.MONTH: Full name of the month in uppercase, right-padded with spaces to a total length of nine characters
10.7.24.Month: Full name of the month with first letter in uppercase, right-padded with spaces to a total length of nine characters
10.7.25.MON: First three letters of the name of the month in uppercase
10.7.26.Mon: First three letters of the name of the month with the first letter in uppercase
10.7.27.RM:Roman numeral month.
10.7.28.WW: Two-digit week of the year
10.7.29.IW: Two-digit ISO week of the year
10.7.30.W: One-digit week of the month
10.7.31.DDD: Three-digit day of the year
10.7.32.DD:Two-digit day of the month
10.7.33.D: One-digit day of the week
10.7.34.DAY: Full name of the day in uppercase
10.7.35.Day: Full name of the day with the first letter in uppercase
10.7.36.DY: First three letters of the name of the day in uppercase
10.7.37.Dy: First three letters of the name of the day with the first letter in uppercase
10.7.38.J: Julian day-the number of days that have passed since January 1, 4713 B.C.
10.7.39.HH24: Two-digit hour in 24-hour format
10.7.40.HH: Two-digit hour in 12-hour format
10.7.41.MI: Two-digit minute
10.7.42.SS: Two-digit second
10.7.43.-/,.;: 'text'
10.7.44.SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYY')
10.7.45.AM or PM: AM or PM as appropriate
10.7.46.SELECT TO_CHAR(SYSDATE, 'PM')
10.7.47.A.M. or P.M.: A.M. or P.M. as appropriate (2)
10.7.48.SELECT TO_CHAR(SYSDATE, 'P.M.') (2)
10.7.49.AD or BC: AD or BC as appropriate
10.7.50.TO_CHAR(SYSDATE, 'BC')
10.7.51.A.D. or B.C.: A.D. or B.C. as appropriate (2)
10.7.52.TO_CHAR(SYSDATE, 'B.C.') (2)
10.7.53.DDSPTH 'of' MONTH, YEAR A.D.
10.7.54.DAY MON, YY AD
10.7.55.SELECT TO_CHAR(TO_DATE('05-FEB-1968'), 'MONTH DD, YYYY')