Oracle Date Formats

I have been writing Oracle SQL for many years but I constantly look up Oracle date formats. It is interesting what your brain stores and what it doesn’t. See below for common ones. I hope this helps someone.

MMNumeric month (e.g., 07)
MONAbbreviated month name (e.g., JUL)
MONTHFull month name (e.g., JULY)
DDDay of month (e.g., 24)
DYAbbreviated name of day (e.g., FRI)
YYYY4-digit year (e.g., 1998)
YYLast 2 digits of the year (e.g., 98)
RRSimilar to YY, but year digits are “rounded” to a year in the range of 1950 to 2049. So, 06 returns 2006 instead of 1906.
AM (or PM)Meridian indicator
HHHour of day (1-12)
HH24Hour of day (0-23)
MIMinute (0-59)
SSSecond (0-59)
These can be used with TO_CHAR or TO_DATE. Like this: TO_DATE(ADDED_DATE, ‘DD-Mon-YYYY HH24:MI:SS’)

Here are some links with more helpful information about Oracle dates.

One thought on “Oracle Date Formats”

Comments are closed.