Date formatting in Oracle can be a tricky and confusing task for many developers and database administrators. With the wide range of options available, it's important to understand the different formats and how to properly display and manipulate dates in your database. In this guide, we will explore the various ways to format dates in Oracle and provide examples of how to use them effectively.
Basic Date Formatting
Oracle uses the TO_DATE function to convert a string to a date. This function takes two parameters: the string to be converted and the format mask. The format mask is used to specify the format of the date string. For example, the format mask 'DD/MM/YYYY' would convert the string '28/02/2021' to the date February 28, 2021.
Let's take a look at some common format masks that can be used with the TO_DATE function:
• DD - day of the month (01-31)
• MM - month (01-12)
• YYYY - four-digit year (e.g. 2021)
• HH - hour (00-23)
• MI - minute (00-59)
• SS - second (00-59)
• AM/PM - specifies whether the time is in the morning or evening
Using these format masks, we can customize the display of our dates to fit our specific needs. For example, if we want to display the date in the format of "month/day/year", we would use the format mask 'MM/DD/YYYY'. This would convert the string '02/28/2021' to the date February 28, 2021.
Formatting Date with Time Zones
Oracle also has the ability to display dates with time zones using the TO_TIMESTAMP_TZ function. This function takes three parameters: the string to be converted, the format mask, and the time zone. The time zone can be specified in different ways, such as a region name (e.g. 'America/Los_Angeles'), an offset from Greenwich Mean Time (e.g. '-08:00'), or a combination of both.
Let's look at an example of how to use the TO_TIMESTAMP_TZ function to display a date with a time zone:
TO_TIMESTAMP_TZ('28/02/2021 10:00:00 AM -08:00', 'DD/MM/YYYY HH:MI:SS AM TZR')
This would convert the string '28/02/2021 10:00:00 AM -08:00' to the date February 28, 2021 at 10:00 AM in the time zone of GMT-08:00.
Formatting Date with Abbreviated Names
In addition to the basic format masks, Oracle also allows for the use of abbreviated names for months and days of the week. This can be useful when you want to display the date in a more concise format. For example, instead of displaying the full month name, you can use the format mask 'MON' to display the abbreviated month name (e.g. 'FEB' for February).
Similarly, you can use 'DY' to display the abbreviated day of the week (e.g. 'MON' for Monday). This can be helpful when you want to show the date in a shorter format, such as 'MON, DD/MM/YYYY' for Monday, February 28, 2021.
Formatting Date with Ordinal Numbers
Oracle also has the ability to display dates with ordinal numbers, which indicate the position of the day within the month (e.g. 1st, 2nd, 3rd). This can be done using the format mask 'DDTH' for day with ordinal suffix (e.g. '28TH' for February 28, 2021).
Using this format mask, we can also display the date with both the day of the week and the ordinal number, such as 'MON, DDTH' for Monday, February 28th.
Conclusion
In this guide, we have explored the different ways to format dates in Oracle using format masks. By understanding these formats and how they work, you can effectively display and manipulate dates in your database. Whether you need to display dates with time zones, abbreviated names, or ordinal numbers, Oracle has a wide range of options to meet your needs. We hope this guide has been helpful in understanding the basics of formatting dates in Oracle. Happy coding!
