Oracle Date Format

Summary: in this tutorial, you will learn about the Oracle date format and various format elements for formatting date data.

Introduction to Oracle Date Format Model

Oracle Database uses an internal format for storing DATE data. Therefore, before inserting date data in a non-standard format into the database, you have to use the TO_DATE() function to convert it to the Oracle’s internal date format.

Similarly, after querying date data from the database, you have to format it to make the date data human-readable. To format a date value, you use the TO_CHAR() function.

Both TO_DATE() and TO_CHAR() functions use something called date format model that describes the format of the date data stored in a character string.

Suppose, you have a literal string that represents a date as follows:

'31-Dec-1999' </code>
Code language: HTML, XML (xml)

The date format model for this string is:

'DD-Mon-YYYY'
Code language: SQL (Structured Query Language) (sql)

A date format model is composed of one or more date format elements. In this example, the format elements are: DD represents the day, Mon represents the abbreviated name of the month, YYYY represents the 4-digit year, and the character string literal '-' represents the hyphen character.

Date Format Elements

If you use a date format element in a specific case e.g., uppercase, lowercase, etc., the output will be in the corresponding case.

For example, the date format element 'DAY' returns uppercase word like TUESDAY; 'Day' returns 'Tuesday'; and 'day' returns 'tuesday'.

The following table illustrates the common date format elements. The example is based on the the date value 01-AUG-2017 15:30:20

ElementDescriptionExample (*)
AD
A.D.
AD indicator with or without periods. AD or A.D.
AM
A.M.
Meridian indicator with or without periods. AM or A.M.
BC
B.C.
BC indicator with or without periods. BC or B.C.
CC
SCC
Two-digit century.  If 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 the 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.2001 returns 21; 2000 returns 20.
DDay of week (1-7). The first day of week can be Sunday or Monday depending on the value of the NLS_TERRITORY parameter.1
DAYName of a day which is controlled by the value of the NLS_DATE_LANGUAGE parameterMonday
DDDay of month (1-31)15
DDDDay of year (1-366)100
DLLong date format determined by the NLS_DATE_FORMAT parameter. Only with the TS element, separated by white space.Tuesday, August 01, 2017
DSShort date format controlled by the NLS_TERRITORY and NLS_LANGUAGE parameters.  Only use with the TS element, separated by white space.8/1/2017
DYAbbreviated name of the day. TUE
EAbbreviated era name e.g., Japanese Imperial, ROC Official, etc.
EEFull era name e.g., Japanese Imperial, ROC Official, etc..
FF [1..9]Fractional seconds
FMReturns a value with no leading or trailing blanks.
FXRequires exact matching between the character data and the format model.
HH
HH12
Hour of day (1-12). 03
HH24Hour of day (0-23). 15
IWWeek of the year (1-52 or 1-53) based on the ISO standard. 31
IYY
IY
I
Last 1, 2, or 3 digits of ISO year. 7 for I, 17 for IY, and 017 for IYY
IYYY4-digit year based on the ISO standard. 2017
JJulian day, which is the number of days since January 1, 4712 BC. 2457967
MIMinute that ranges from 0 to 59 30
MMMonth that ranges from 01 through 12, where January is 01. 08
MONAbbreviated name of the month. AUG
MONTHName of the month. AUGUST
PM

P.M.

Meridian indicator with or without periods.
QQuarter of year (1, 2, 3, 4; January – March = 1). 3
RMRoman numeral month (I-XII; January = I). VIII
RRallows you to store 20th-century dates in the 21st century using only two digits.
RRRRRound year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.
SSSecond (0-59). 20
SSSSSSeconds past midnight (0-86399). 55820
TSTim in the short time format, depending on the NLS_TERRITORY and NLS_LANGUAGE parameters. Only use with the DL or DS element, separated by white space. 3:30:00 PM

TZD

Daylight savings information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in DATE formats. PST (for US/Pacific standard time); PDT (for US/Pacific daylight time)

TZH

Time zone hour. (See TZM format element.) Valid in timestamp and interval formats, but not in DATE formats. ‘HH:MI:SS.FFTZH:TZM’

TZM

Time zone minute. (See TZH format element.) Valid in timestamp and interval formats, but not in DATE formats.

TZR

Time zone region information. The value must be one of the time zone regions supported in the database. Valid in timestamp and interval formats, but not in DATE formats.

Example: US/Pacific

WW

The week number of a year that ranges from 1 to 53. The week 1 starts on the first day of the year and continues to the seventh day of the year. 31

W

The week number of a month that ranges from 1 to 5. The week 1 starts on the first day of the month and ends on the seventh. 1

Y,YYY

4-digit year with a comma.2,017

YEAR SYEAR

Year, spelled out; S prefixes BC dates with a minus sign (-). TWENTY SEVENTEEN

YYYY SYYYY

4-digit year; S prefixes BC dates with a minus sign.YYYY returns 2017
Y
YY
YYY
The last 1, 2, or 3 digits of a year. Y returns 7, YY returns 17, and YYY returns 017

Besides the standard date format elements, you can include the following character in the date format model.

  • Punctuation such as hyphen (-), slash (/), comma (,), period (.) and colons (:)
  • Character string literals enclosed in double quotation marks.

These punctuation and characters appear in the return value in the same location as they appear in the format model.

It is not valid to use the same format element twice and combine format elements that represent the similar information for input date models. For example, you cannot use both ‘SYYYY’ and ‘BC’ in the same date format model.

In this tutorial, you have learned how to use the Oracle Date Format to format date data for storing and displaying.

Was this tutorial helpful?