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 need to use the TO_DATE() function to convert it to Oracle’s internal date format.

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

Both TO_DATE() and TO_CHAR() functions use a date format model describing 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 language: SQL (Structured Query Language) (sql)

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 words like TUESDAY; 'Day' returns 'Tuesday'; and 'day' returns 'tuesday'.

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

Element	Description	Example (*)
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.	2000 returns 20; 2001 returns 21;
D	Day of week (1-7), where 1 is Sunday (depends on NLS_TERRITORY)	1
DAY	Name of a day that is controlled by the value of the `NLS_DATE_LANGUAGE` parameter	MONDAY
DD	Day of month (1-31)	15
DDD	Day of year (1-366)	100
DL	Long date format based on `NLS_DATE_FORMAT`. Only with the `TS` element, separated by white space.	Tuesday, August 01, 2017
DS	Short date format based on `NLS_TERRITORY` and `NLS_LANGUAGE` parameters. Only used with the `TS` element, separated by white space.	8/1/2017
DY	Abbreviated name of the day.	TUE
E	Abbreviated era name e.g., Japanese Imperial, ROC Official, etc.	和 (Japanese era)
EE	Full era name e.g., Japanese Imperial, ROC Official, etc..	平成 (Heisei)
FF [1..9]	Fractional seconds	.123456789
FM	Returns a value with no leading or trailing blanks.
FX	Requires exact matching between the character data and the format model.
HH, HH12	Hour of day (1-12).	03
HH24	Hour of day (0-23).	15
IW	Week of the year (1-52 or 1-53) based on the ISO standard.	31
I, IY, IYY	Last 1, 2, or 3 digits of ISO year.	I → 7, IY → 17, IYY → 017
IYYY	4-digit year based on the ISO standard.	2017
J	Julian day, which is the number of days since January 1, 4712 BC.	2457967
MI	Minute (0-59)	30
MM	Month (01–12; Jan = 01)	08
MON	Abbreviated month name	AUG
MONTH	Full month name, padded with blanks	AUGUST
PM, P.M.	Meridian indicator for PM with or without periods
Q	Quarter of year (1–4)	3
RM	Roman numeral month (I-XII; January = I).	VIII
RR	2-digit year with century inference for 1950–2049	99 → 1999, 01 → 2001
RRRR	4-digit year like YYYY (not time zone-related)	2017
SS	Seconds (0–59)	20
SSSSS	Seconds since midnight (0–86399)	55820
TS	Tim 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	`PST` (for US/Pacific Standard Time); `PDT` (for US/Pacific daylight time)	PST, PDT
TZH	Time zone hour (used in timestamps)	+02
TZM	Time zone minute (used in timestamps)	00
TZR	Time zone region name	US/Pacific
WW	Week of year (1–53), starting from Jan 1	31
W	Week of month (1–5); first week starts on 1st of the month	2
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; SYYYY includes minus for BC	2017 or -0044
Y, YY, YYYY	Last 1, 2, or 3 digits of the year	7, 17, 017

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

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

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

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

AD #

SELECT
  TO_CHAR (
    TO_DATE ('2017-08-01 15:30:20', 'YYYY-MM-DD HH24:MI:SS'),
    'AD'
  ) RESULT;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
ADCode language: SQL (Structured Query Language) (sql)

AM #

SELECT
  TO_CHAR (
    TO_DATE ('2017-08-01 15:30:20', 'YYYY-MM-DD HH24:MI:SS'),
    'AM'
  ) RESULT;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
PM
Code language: SQL (Structured Query Language) (sql)

BC #

SELECT
  TO_CHAR (
    TO_DATE ('2017-08-01 15:30:20', 'YYYY-MM-DD HH24:MI:SS'),
    'BC'
  ) RESULT;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
AD
Code language: SQL (Structured Query Language) (sql)

CC #

SELECT
  TO_CHAR (
    TO_DATE ('2017-08-01 15:30:20', 'YYYY-MM-DD HH24:MI:SS'),
    'CC'
  ) RESULT;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
21Code language: SQL (Structured Query Language) (sql)

D – Day of the week #

SELECT
  TO_CHAR (
    TO_DATE ('2017-08-01 15:30:20', 'YYYY-MM-DD HH24:MI:SS'),
    'D'
  ) RESULT;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
3Code language: SQL (Structured Query Language) (sql)

DAY – Day Name #

SELECT
  TO_CHAR (
    TO_DATE ('2017-08-01 15:30:20', 'YYYY-MM-DD HH24:MI:SS'),
    'DAY'
  ) RESULT;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT   
---------
TUESDAY  Code language: SQL (Structured Query Language) (sql)

DD – Day of the month (1–31) #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'DD') RESULT
FROM
  dual;Code language: JavaScript (javascript)

Output:

RESULT
------
01

DDD – Day of the year (1–366) #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'DDD') RESULT
FROM
  dual;Code language: JavaScript (javascript)

Output:

RESULT
------
213

August 1 is the 213th day of the year in a non-leap year (2017).

DL – Long date format (NLS-dependent) #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'DL') RESULT
FROM
  dual;Code language: JavaScript (javascript)

Sample Output (may vary by NLS settings).

DS – Short date format (NLS-dependent) #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'DS') RESULT
FROM
  dual;Code language: JavaScript (javascript)

Sample Output (may vary by NLS settings):

DY – Abbreviated name of the day #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'DY') RESULT
FROM
  dual;Code language: JavaScript (javascript)

Output:

RESULT
------
TUE

D – Day of the week (1–7, NLS-dependent: 1 = Sunday by default) #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'D') RESULT
FROM
  dual;Code language: JavaScript (javascript)

Output:

RESULT
------
3

2017-08-01 is a Tuesday, and in most US-based NLS settings, Sunday = 1, so Tuesday = 3.

E – Abbreviated era name #

Only works for certain NLS calendars like Japanese Imperial, ROC, etc.

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'E') RESULT
FROM
  dual;Code language: JavaScript (javascript)

EE – Full Era Name #

Only works for certain NLS calendars like Japanese Imperial, ROC, etc.

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'EE') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

FF1 – 1 digit of fractional seconds #

SELECT
  TO_CHAR (
    TO_TIMESTAMP (
      '2017-08-01 15:30:20.123456',
      'YYYY-MM-DD HH24:MI:SS.FF'
    ),
    'FF1'
  ) RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
1Code language: SQL (Structured Query Language) (sql)

FM – No padding #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'FMDay') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
-------
Tuesday
Code language: SQL (Structured Query Language) (sql)

FX – Exact match (used with TO_DATE) #

SELECT
  TO_DATE ('2017-08-01 15:30:20', 'FXYYYY-MM-DD HH24:MI:SS') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
-------------------
01-AUG-17 03.30.20 PM
Code language: SQL (Structured Query Language) (sql)

HH / HH12 – Hour (1–12) #

SELECT
  TO_CHAR (
    TO_DATE ('2017-08-01 03:30:20', 'YYYY-MM-DD HH24:MI:SS'),
    'HH'
  ) RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
03
Code language: SQL (Structured Query Language) (sql)

HH24 – Hour (0–23) #

SELECT
  TO_CHAR (
    TO_DATE ('2017-08-01 15:30:20', 'YYYY-MM-DD HH24:MI:SS'),
    'HH24'
  ) RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
15
Code language: SQL (Structured Query Language) (sql)

IW – ISO week number #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'IW') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
31
Code language: SQL (Structured Query Language) (sql)

I, IY, IYY, IYYY – ISO year parts #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'I') RESULT
FROM
  dual;

-- Output: 7
SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'IY') RESULT
FROM
  dual;

-- Output: 17
SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'IYY') RESULT
FROM
  dual;

-- Output: 017
SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'IYYY') RESULT
FROM
  dual;

-- Output: 2017Code language: SQL (Structured Query Language) (sql)

J – Julian Day #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'J') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
--------
2457967
Code language: SQL (Structured Query Language) (sql)

MI – Minutes #

SELECT
  TO_CHAR (
    TO_DATE ('2017-08-01 15:30:20', 'YYYY-MM-DD HH24:MI:SS'),
    'MI'
  ) RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
30Code language: SQL (Structured Query Language) (sql)

MM – Month number #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'MM') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
08
Code language: SQL (Structured Query Language) (sql)

MON – Abbreviated month #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'MON') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
AUG
Code language: SQL (Structured Query Language) (sql)

MONTH – Full month name #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'MONTH') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
----------
AUGUST
Code language: SQL (Structured Query Language) (sql)

PM / P.M. – Meridian indicator (uppercase) #

SELECT
  TO_CHAR (
    TO_DATE ('2017-08-01 15:30:20', 'YYYY-MM-DD HH24:MI:SS'),
    'PM'
  ) RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
PM
Code language: SQL (Structured Query Language) (sql)

Q – Quarter of the year #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'Q') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
3
Code language: SQL (Structured Query Language) (sql)

RM – Roman numeral month #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'RM') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
VIII
Code language: SQL (Structured Query Language) (sql)

RR – Two-digit year, special century handling #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'RR') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
17
Code language: SQL (Structured Query Language) (sql)

RRRR – Treated the same as YYYY (4-digit year) #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'RRRR') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
2017
Code language: SQL (Structured Query Language) (sql)

SS – Seconds (0–59) #

SELECT
  TO_CHAR (
    TO_DATE ('2017-08-01 15:30:20', 'YYYY-MM-DD HH24:MI:SS'),
    'SS'
  ) RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
20
Code language: SQL (Structured Query Language) (sql)

SSSSS – Seconds past midnight #

SELECT
  TO_CHAR (
    TO_DATE ('2017-08-01 15:30:20', 'YYYY-MM-DD HH24:MI:SS'),
    'SSSSS'
  ) RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
-------
55820
Code language: SQL (Structured Query Language) (sql)

TS – Short time format (based on NLS) #

SELECT
  TO_CHAR (
    TO_DATE ('2017-08-01 15:30:20', 'YYYY-MM-DD HH24:MI:SS'),
    'TS'
  ) RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output (NLS-dependent, example below):

RESULT
----------
3:30:20 PM
Code language: SQL (Structured Query Language) (sql)

WW – Week number of year (starts from Jan 1) #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'W') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
31
Code language: SQL (Structured Query Language) (sql)

W – Week number of month (starts from 1st) #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'W') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
1
Code language: SQL (Structured Query Language) (sql)

Y, YY, YYY – Last 1, 2, or 3 digits of year #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'Y') RESULT
FROM
  dual;

-- Output: 7
SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'YY') RESULT
FROM
  dual;

-- Output: 17
SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'YYY') RESULT
FROM
  dual;

-- Output: 017Code language: SQL (Structured Query Language) (sql)

YYYY / SYYYY – 4-digit year (S adds minus for BC) #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'YYYY') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------
2017
Code language: SQL (Structured Query Language) (sql)

YEAR / SYEAR – Year spelled out #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'YEAR') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
------------
TWENTY SEVENTEEN
Code language: SQL (Structured Query Language) (sql)

Y,YYY – 4-digit year with comma #

SELECT
  TO_CHAR (TO_DATE ('2017-08-01', 'YYYY-MM-DD'), 'Y,YYY') RESULT
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT
-------
2,017
Code language: SQL (Structured Query Language) (sql)

Summary #

Use the Oracle Date Format to format date data for storing and displaying.

Was this tutorial helpful?