Oracle date format

Let’s review Oracle date formatting.
This is useful for functions like TO_DATE, TO_CHAR or alter session to modify nls_date_format.

Format table

Formato Descripción Resultado
Y Last digit of the year 7
YY Last two digits of the year 17
YYY Last three digits of the year 017
YYYY Four-digit year 2017
RR Two-digit year 17
YEAR Year in English TWENTY SEVENTEEN
MM Two-digit month 02
MON Three-letter month FEB
MONTH Month in English FEBRUARY
D Day of the week 5
DD Day of the month (2 digits) 07
DDD Day of the year 212
DY Three-letter day of the week FRI
DAY Day in English FRIDAY
W Week of the month 3
WW Week of the year 43
Q Quarter of the year 1
CC Century 10
SCC Same as CC, but it adds a minus in case of BC -3
SYYYY Same as YYYY, but it adds a minus in case of BC -734
SYEAR Same as YEAR, but it adds a minus in case of BC -THIRTY THREE
BC, AD, B.C. , A.D. Adds BC, AD, B.C. or A.D. AD
J Days since 31 December 4713 BC 1356021
IW ISO standard week 1-53 32
RM Roman numeral month II
AM, PM, A.M. , P.M. Meridian indicators PM
HH Hour of day 03
HH12 Hour of day 1-12 11
HH24 Hour of day 0-23 14
MI Minute 0-59 34
SS Second 0-59 58
SSSSS Seconds since midnight 0-86399 32098
/.,?#! Punctuation marks, for instance: ‘MM.YY’  14.10
“literals” For instance, ‘”Week” W “of” Month’ Week 3 of February
TH DDth 12TH
SP  Spelled out number, for instance, MM Nine
THSP or SPTH Spelled out positional or ordinal number, for example: ‘hh24Spth’ Fourtheenth

Example

We are going to alter our session to modify date format.
In this case we are connected to sqlplus, and this is what we execute:

alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Dates will be represented like: 18/12/2017 14:27:17

Leave a Reply

Your email address will not be published. Required fields are marked *