17,000 Oracle Timestamp Format Examples

You will love this blog because date, timestamp functions and formatting differ dramatically between databases, but I have covered that with brilliant examples and clear and concise explanations below.

All of these examples have come from my books and training classes. Please do me a favor and tell your training coordinator that you know the best technical trainer in the world. Ask them to hire me to train at your company, either on-site or with a virtual class. They can see our classes, outlines, and a sample of my teaching at this link on our website.

https://coffingdw.com/education/

Oracle uses the TO_CHAR command to transform raw date or timestamp data to format it as you want it presented.

For example, TO_CHAR(CURRENT_TIMESTAMP, ‘YYYY-MM-DD HH:MI:SS’)  would format the current timestamp as ‘YYYY-MM-DD HH:MI:SS’, and might result in output such as this format 2024-04-12 01:48:19. You can tailor the output by including various format specifiers for years, months, days, hours, minutes, seconds, and more.

Below are some great examples for formatting an Oracle timestamp in which I have color coded for you to show the individual components.

Here is an explanation of the components:
YYYY: Represents the year in four digits (e.g., 2024).
YY: Represents the year in two digits (e.g., 24).
MM: Represents the month in two digits (e.g., 04 for April).
DD: Represents the day of the month in two digits (e.g., 12).
HH: Represents the hour of the day in two digits using a 12-hour clock (e.g., 01 for 1:00 PM).
HH24: Represents the hour of the day in two digits using a 24-hour clock (e.g., 13 for 1:00 PM).
HH12: Represents the hour of the day in two digits using a 12-hour clock (e.g., 01 for 1:00 PM).
MI: Represents the minutes in two digits.
SS: Represents the seconds in two digits.
Month: Name of month capitalized, padded with blanks to length of 9 characters.
Mon: Name of month abbreviated and capitalized.
DY: Day of the week abbreviated all in uppercase.
DAY: Full name of the day of the week in uppercase padded with blanks to length of 9 characters.
Day: Full name of the day of the week capitalized padded with blanks to length of 9 characters.
RM: Roman numeral representation of the month.
FF2: Fractional seconds to two digits.
FF3: Fractional seconds to three digits.
FF4: Fractional seconds to four digits.
FF9: Fractional seconds to nine digits.
AM: AM/PM indicator.
TZH:TZM: Time zone hour and minute offset.

I bet you didn’t know you could use the TO_CHAR function in the WHERE and AND clauses to filter rows. The query below only returns rows where the customer_timestamp has a month of December, a day of Saturday, a minute of 54, seconds of 35, and milliseconds of 000.

Below is a table with over 132, 000 examples of Oracle Timestamps formatted and their results.