Formatting Dates in Oracle Database - Quick Guide

Did you ever find yourself getting frustrated when trying to format dates in Oracle Database? It’s a common sticking point. Not to worry, this guide will set you straight.

Introduction to Oracle’s DATE Data Type

Before we start formatting, it’s important to understand the DATE data type in Oracle. The DATE data type is used to store the date and time for a specific instance. Unlike some databases (we’re looking at you MySQL), Oracle’s DATE data type includes time information along the date.

The storage format of a DATE data type is not directly visible to developers, but Oracle provides a function called TO_CHAR which can be used to display the date in a readable format.

SELECT TO_CHAR(sysdate, 'DD/MM/YYYY HH24:MI:SS') "Current Date" FROM dual; 

This SQL query will return the current system date and time in the ‘DD/MM/YYYY HH24:MI:SS’ format.

Understanding Oracle’s Date Format Codes

The real magic in Oracle date formatting comes from the different format codes available. You’ve already seen ‘DD’, ‘MM’, ‘YYYY’, ‘HH24’, ‘MI’, and ‘SS’ in action. Below is a list of some other useful format codes:

• DD: Day of month (1-31)
• MM: Month (01-12. January is 01)
• YYYY: 4-digit year
• YY: Last 2 digits of the year
• HH24: Hour of day (00-23)
• MI: Minute (00-59)
• SS: Second (00-59)

By combining these codes, we can format dates in a variety of ways. For instance, if you wanted the date in the format ‘month day, year’, you could use the following command:

SELECT TO_CHAR(sysdate, 'MM DD, YYYY') "Current Date" FROM dual; -- 01 22, 1983

Using TO_DATE for Formatting

Alternatively, if you have a string that represents a date and you want to convert it into a DATE data type, you would use the TO_DATE function:

SELECT TO_DATE('2021/07/16', 'YYYY/MM/DD') "Formatted Date" FROM dual;

This will return a result of ‘16-JULY-21’ – the default format of Oracle DATE. Note that the TO_DATE function allows you to specify the format of the input string so that Oracle can accurately convert it into a DATE data type.

Formatting Dates with Alternative Formats

You’re not limited to the English language or the Gregorian calendar when it comes to date formats in Oracle. Instead, you can use different date language and date territory to format your date:

SELECT TO_CHAR(sysdate, 'MONTH DD, YYYY', 'NLS_DATE_LANGUAGE = German') "Current Date" FROM dual;

This code formats the current date in German-language naming conventions.

Understanding how to format dates is an important aspect of working with databases, and Oracle Database provides a diverse set of tools to help you master this task. Don’t be afraid to experiment with different date format codes in order to understand better how they work and to refine your SQL abilities.

And that’s the end of it! You should now have a solid understanding of how to format dates in Oracle Database. The TO_CHAR and TO_DATE functions, combined with an understanding of Oracle’s various date format codes, offer a powerful toolset for handling dates. Happy querying!