{"id":394,"date":"2017-08-01T22:09:23","date_gmt":"2017-08-01T15:09:23","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=394"},"modified":"2025-05-10T17:48:23","modified_gmt":"2025-05-11T00:48:23","slug":"oracle-date-format","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/oracle-basics\/oracle-date-format\/","title":{"rendered":"Oracle Date Format"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn about the Oracle date format and various format elements for formatting date data.<\/p>\r\n\r\n\r\n\r\n

Introduction to Oracle Date Format Model #<\/a><\/h2>\r\n\r\n\r\n\r\n

Oracle Database uses an internal format for storing DATE<\/a><\/code> data. Therefore, before inserting date data in a non-standard format into the database, you need to use the TO_DATE()<\/code> function to convert it to Oracle’s internal date format.<\/p>\r\n\r\n\r\n\r\n

Similarly, after querying the date data from the database, you need to use the TO_CHAR()<\/code> function to format it to make the date data human-readable.<\/p>\r\n\r\n\r\n\r\n

Both TO_DATE()<\/code> and TO_CHAR()<\/code> functions use a date format model describing the format of the date data stored in a character string.<\/p>\r\n\r\n\r\n\r\n

Suppose you have a literal string that represents a date as follows:<\/p>\r\n\r\n\r\n

'31-Dec-1999'<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The date format model for this string is:<\/p>\r\n\r\n\r\n
'DD-Mon-YYYY'<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
A date format model is composed of one or more date format elements. In this example, the format elements are: DD<\/code> represents the day, Mon<\/code> represents the abbreviated name of the month, YYYY<\/code> represents the 4-digit year, and the character string literal\u00a0'-'<\/code>\u00a0represents the hyphen character.<\/p>\r\n\r\n\r\n\r\n
Date Format Elements #<\/a><\/h2>\r\n\r\n\r\n\r\n
If you use a date format element in a specific case, e.g., uppercase, lowercase, etc., the output will be in the corresponding case.<\/p>\r\n\r\n\r\n\r\n
For example, the date format element 'DAY'<\/code> returns uppercase words like TUESDAY<\/code>; 'Day'<\/code> returns 'Tuesday'<\/code>; and 'day'<\/code> returns 'tuesday'<\/code>.<\/p>\r\n\r\n\r\n\r\n
The following table shows the date format elements. The example is based on the date value 01-AUG-2017 15:30:20<\/code><\/p>\r\n\r\n\r\n\r\n
\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
Element<\/th>\r\nDescription<\/th>\r\nExample (*)<\/th>\r\n<\/tr>\r\n<\/thead>\r\n
AD, A.D.<\/td>\r\nAD indicator with or without periods.<\/td>\r\n\u00a0AD or A.D.<\/td>\r\n<\/tr>\r\n
AM, A.M.<\/td>\r\nMeridian indicator with or without periods.<\/td>\r\n\u00a0AM or A.M.<\/td>\r\n<\/tr>\r\n
BC, B.C.<\/td>\r\nBC indicator with or without periods.<\/td>\r\n\u00a0BC or B.C.<\/td>\r\n<\/tr>\r\n
CC, SCC<\/td>\r\nTwo-digit century. \u00a0If the last 2 digits of a y-digit year are 00, the century is the same as the first 2 digits e.g., 2000 returns 20. In case\u00a0the last 2 digits are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year e.g., 2001 returns 21.<\/td>\r\n2000 returns 20; 2001 returns 21;<\/td>\r\n<\/tr>\r\n
D<\/td>\r\nDay of week (1-7), where 1 is Sunday (depends on NLS_TERRITORY)<\/td>\r\n1<\/td>\r\n<\/tr>\r\n
DAY<\/td>\r\nName of a day that is controlled by the value of the NLS_DATE_LANGUAGE<\/code> parameter<\/td>\r\nMONDAY<\/td>\r\n<\/tr>\r\n
DD<\/td>\r\nDay of month (1-31)<\/td>\r\n15<\/td>\r\n<\/tr>\r\n
DDD<\/td>\r\nDay of year (1-366)<\/td>\r\n100<\/td>\r\n<\/tr>\r\n
DL<\/td>\r\nLong date format based on NLS_DATE_FORMAT<\/code>. Only with the TS<\/code> element, separated by white space.<\/td>\r\nTuesday, August 01, 2017<\/td>\r\n<\/tr>\r\n
DS<\/td>\r\nShort date format based on NLS_TERRITORY<\/code> and NLS_LANGUAGE<\/code> parameters. Only used with the TS<\/code> element, separated by white space.<\/td>\r\n8\/1\/2017<\/td>\r\n<\/tr>\r\n
DY<\/td>\r\nAbbreviated name of the day.<\/td>\r\n\u00a0TUE<\/td>\r\n<\/tr>\r\n
E<\/td>\r\nAbbreviated era name e.g., Japanese Imperial, ROC Official, etc.<\/td>\r\n\u548c (Japanese era)<\/td>\r\n<\/tr>\r\n
EE<\/td>\r\nFull era name e.g., Japanese Imperial, ROC Official, etc..<\/td>\r\n\u5e73\u6210 (Heisei)<\/td>\r\n<\/tr>\r\n
FF [1..9]<\/td>\r\nFractional seconds<\/code><\/td>\r\n.123456789<\/td>\r\n<\/tr>\r\n
FM<\/td>\r\nReturns a value with no leading or trailing blanks.<\/td>\r\n\u00a0<\/td>\r\n<\/tr>\r\n
FX<\/td>\r\nRequires exact matching between the character data and the format model.<\/td>\r\n\u00a0<\/td>\r\n<\/tr>\r\n
HH, HH12<\/td>\r\nHour of day (1-12).<\/td>\r\n\u00a003<\/td>\r\n<\/tr>\r\n
HH24<\/td>\r\nHour of day (0-23).<\/td>\r\n\u00a015<\/td>\r\n<\/tr>\r\n
IW<\/td>\r\nWeek of the year (1-52 or 1-53) based on the ISO standard.<\/td>\r\n\u00a031<\/td>\r\n<\/tr>\r\n
I, IY, IYY<\/td>\r\nLast 1, 2, or 3 digits of ISO year.<\/td>\r\nI \u2192 7, IY \u2192 17, IYY \u2192 017<\/td>\r\n<\/tr>\r\n
IYYY<\/td>\r\n4-digit year based on the ISO standard.<\/td>\r\n\u00a02017<\/td>\r\n<\/tr>\r\n
J<\/td>\r\nJulian day<\/a>, which is the number of days since January 1, 4712 BC.<\/td>\r\n\u00a02457967<\/td>\r\n<\/tr>\r\n
MI<\/td>\r\nMinute (0-59)<\/td>\r\n\u00a030<\/td>\r\n<\/tr>\r\n
MM<\/td>\r\nMonth (01\u201312; Jan = 01)<\/td>\r\n\u00a008<\/td>\r\n<\/tr>\r\n
MON<\/td>\r\nAbbreviated month name<\/td>\r\n\u00a0AUG<\/td>\r\n<\/tr>\r\n
MONTH<\/td>\r\nFull month name, padded with blanks<\/td>\r\n\u00a0AUGUST<\/td>\r\n<\/tr>\r\n
PM, P.M.<\/span><\/td>\r\nMeridian indicator for PM with or without periods<\/td>\r\n\u00a0<\/td>\r\n<\/tr>\r\n
Q<\/td>\r\nQuarter of year (1\u20134)<\/td>\r\n\u00a03<\/td>\r\n<\/tr>\r\n
RM<\/td>\r\nRoman numeral month (I-XII; January = I).<\/td>\r\n\u00a0VIII<\/td>\r\n<\/tr>\r\n
RR<\/td>\r\n2-digit year with century inference for 1950\u20132049<\/td>\r\n99 \u2192 1999, 01 \u2192 2001<\/td>\r\n<\/tr>\r\n
RRRR<\/td>\r\n4-digit year like YYYY (not time zone-related)<\/td>\r\n2017<\/td>\r\n<\/tr>\r\n
SS<\/td>\r\nSeconds (0\u201359)<\/td>\r\n\u00a020<\/td>\r\n<\/tr>\r\n
SSSSS<\/td>\r\nSeconds since midnight (0\u201386399)<\/td>\r\n55820<\/td>\r\n<\/tr>\r\n
TS<\/td>\r\nTim in the short time format, depending on the NLS_TERRITORY<\/code> and NLS_LANGUAGE<\/code> parameters. Only use with the DL<\/code> or DS<\/code> element, separated by white space.<\/td>\r\n\u00a03:30:00 PM<\/td>\r\n<\/tr>\r\n
TZD<\/td>\r\n\u00a0PST<\/code> (for US\/Pacific Standard Time); PDT<\/code> (for US\/Pacific daylight time)<\/td>\r\nPST, PDT<\/td>\r\n<\/tr>\r\n
TZH<\/td>\r\nTime zone hour (used in timestamps)<\/td>\r\n+02<\/td>\r\n<\/tr>\r\n
TZM<\/td>\r\nTime zone minute (used in timestamps)<\/td>\r\n00<\/td>\r\n<\/tr>\r\n
TZR<\/td>\r\nTime zone region name<\/td>\r\nUS\/Pacific<\/td>\r\n<\/tr>\r\n
WW<\/td>\r\nWeek of year (1\u201353), starting from Jan 1<\/td>\r\n\u00a031<\/td>\r\n<\/tr>\r\n
\r\n
W<\/p>\r\n<\/td>\r\n
Week of month (1\u20135); first week starts on 1st of the month<\/td>\r\n2<\/td>\r\n<\/tr>\r\n
Y,YYY<\/td>\r\n4-digit year with a comma<\/td>\r\n2,017<\/td>\r\n<\/tr>\r\n
YEAR SYEAR<\/td>\r\nYear, spelled out; S<\/code> prefixes BC dates with a minus sign (-).<\/td>\r\n\u00a0TWENTY SEVENTEEN<\/td>\r\n<\/tr>\r\n
YYYY, SYYYY<\/td>\r\n4-digit year; SYYYY includes minus for BC<\/td>\r\n2017 or -0044<\/td>\r\n<\/tr>\r\n
Y, YY, YYYY<\/td>\r\nLast 1, 2, or 3 digits of the year<\/td>\r\n7, 17, 017<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<\/figure>\r\n\r\n\r\n\r\n
Besides the standard date format elements, you can include the following characters in the date format model.<\/p>\r\n\r\n\r\n\r\n