Guide to Effective Date Formatting in Oracle

Dates are fundamental to many database applications, and correctly formatting them is crucial for ensuring data accuracy and efficient data retrieval. Oracle provides a plethora of date functions and formatting options to choose from, which can be overwhelming without proper guidance.

In this article, we’ll cover everything you need to know about date formatting in Oracle, including the different date data types, formatting options, and practical examples of how to use them. By the end of this guide, you’ll have a solid understanding of date formatting in Oracle, allowing you to effectively work with dates in your database applications.

Contents

• Date formats in Oracle
• Default date format
  • Custom date formats
  • TO_CHAR function
  • TO_DATE function
  • Date literals
  • Customize date display formats in dbForge Studio for Oracle
• Handle time zones in Oracle
  • Time zone abbreviations
  • Time zone offset values
• Best practices for date formatting in Oracle
  • Bonus: A few tricks
    • Return the day name
    • Return the month name
    • Manage case
• Conclusion

Date formats in Oracle

In Oracle, both date and time are supported in a different way than the SQL2 standard. Unlike using two separate entities, date and time, Oracle utilizes only one, called DATE. The DATE type is stored in a specific internal format that includes not only the month, day, and year but also the hour, minute, and second. This means that with the DATE data type, you can work with both dates and times without having to use separate entities.

The DATE data type in Oracle allows you to store date and time values with a precision of up to one second. It stores the year (including the century), month, day, hour, minute, and second information in a proprietary format using fixed-length fields of seven bytes. The range of the DATE data type in Oracle is from January 1, 4712 BCE through December 31, 9999 CE (Common Era or “AD”). If BCE is not explicitly specified, Oracle defaults to CE date entries.

Oracle’s proprietary format for storing date data is comprised of seven bytes, each corresponding to a specific field – century, year, month, day, hour, minute, and second. This allows for efficient storage and retrieval of date and time information in Oracle Database.

Default date format

In Oracle, the standard date format for both input and output is DD-MON-YY, for example, 01-JAN-23. This format is controlled by the value of the NLS_DATE_FORMAT parameter, which can be set at the session or system level.

To view the current value of the NLS_DATE_FORMAT parameter, you can execute the following SQL statement:

SELECT
  value
FROM
  V$NLS_PARAMETERS
WHERE
  parameter = 'NLS_DATE_FORMAT';

This statement will return the current value of the parameter, which can be customized to display dates in a different format using the ALTER SESSION command. It’s important to note that changing the NLS_DATE_FORMAT parameter affects the display of all date values in the current session, so it’s important to use it with caution when making changes.

As you can see, in our database system, the value of NLS_DATE_FORMAT is:

DD-MON-RR

In Oracle, the SYSDATE function can be used to retrieve the current date and time from the database server. By default, the date value returned by the SYSDATE function is displayed in the default date format for the current session.

To retrieve the current date in the standard date format, you can use the following SQL statement:

SELECT
  sysdate
FROM
  dual;

Custom date formats

In Oracle, you can use the ALTER SESSION statement to change the value of the NLS_DATE_FORMAT parameter, which controls the default format for displaying dates. This can be useful if you need to customize the date format to meet specific requirements in your database application.

For example, suppose you want to change the standard date format from DD-MON-YY to YYYY-MM-DD. You can use the following SQL statement to change the value of the NLS_DATE_FORMAT parameter for the current session:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

After changing the NLS_DATE_FORMAT parameter, you can verify that the change has taken effect by executing the statement you already know to display the current system date:

SELECT
  sysdate
FROM
  dual;

If the NLS_DATE_FORMAT parameter has been successfully changed to YYYY-MM-DD, the output should display the current system date in that format, such as:

TO_CHAR function

The TO_CHAR() function in Oracle allows you to format a DATE value in a specific way and return it as a string. For example, you can use the following query to display the current system date in a particular format:

SELECT
  TO_CHAR( SYSDATE, 'FMMonth DD, YYYY' )
FROM
  dual;

This will return a string in the format ‘Month DD, YYYY’, where the month is spelled out in full and the year is displayed with four digits, such as ‘March 17, 2023’. The format mask used in the TO_CHAR() function specifies how the date should be output.

The language used to display the month name is determined by the NLS_DATE_LANGUAGE parameter. You can check the current value of this parameter by executing the following query:

SELECT
  value
FROM
  V$NLS_PARAMETERS
WHERE
  parameter = 'NLS_DATE_LANGUAGE';

If you want to display the month names in a different language, such as German, for example, you can use the ALTER SESSION statement to change the value of the NLS_DATE_LANGUAGE parameter, as follows:

ALTER SESSION SET NLS_DATE_LANGUAGE = 'GERMAN';

After executing this statement, you can run the same TO_CHAR() query again to see the effect:

As you can see, the name of the month has been changed from American to German.

TO_DATE function

Oracle stores DATE data using an internal format, which means that you may need to convert a string to a date value before storing it in a date column. The TO_DATE() function can be used to convert date values that are not in the standard format to the required format.

For instance, if you have a string value ‘March 17, 2023’, you can convert it to a corresponding date value using the following SQL query with the TO_DATE() function:

SELECT TO_DATE('March 17, 2023', 'MONTH DD, YYYY') FROM dual;

The output value will be ’17-Mar-23′, which is the standard date format used by Oracle.

Note that you need to specify the format mask in the second parameter of the TO_DATE() function. The format mask corresponds to the format of the string value you are trying to convert.

By using the TO_DATE() function, you can easily convert string values to date values in Oracle, ensuring accurate data storage and manipulation.

Date literals

In Oracle, you can also specify a DATE value as a string literal using the syntax DATE 'YYYY-MM-DD'. This can be useful in situations where you need to specify a date value in a SQL query without explicitly converting a string to a date using the TO_DATE() function.

Note that when using the ANSI date literal, the date must be in the YYYY-MM-DD format and the Gregorian calendar must be used. However, if you need to include time data in a date value or if the date is not in the required format, you will need to use the TO_DATE() function as discussed earlier.

Customize date display formats in dbForge Studio for Oracle

If you use dbForge Studio for Oracle, you can easily customize the display of date formats without needing to know any commands. You can do everything through the graphical user interface (GUI). For this, go to Tools -> Options -> Data Editor -> Formatting. Then, choose the format you want the date to be displayed in from the Predefined formats list. You can check the result in the Test Input field and click OK if you’re satisfied with it. Keep in mind that the options you set through the menu will take priority over any commands you use in the SQL Editor.

Note
If you want to learn more about working with Oracle dates, we recommend checking out the How to compare dates in Oracle article.

Handle time zones in Oracle

Dealing with time zones is an essential aspect of working with date and time data, and Oracle provides various functions to handle time zones. Oracle stores all timestamps in Coordinated Universal Time (UTC). When you insert a timestamp into a database, Oracle automatically converts the timestamp to UTC. When you query a timestamp, Oracle converts it back to your local time zone.

To handle time zones in Oracle, you can use the AT TIME ZONE function. It takes a TIMESTAMP WITH TIME ZONE value and a time zone name or offset and returns a new TIMESTAMP WITH TIME ZONE value converted to the specified time zone.

Here’s the basic syntax of the AT TIME ZONE function:

SELECT timestamp AT TIME ZONE source_tz AT TIME ZONE target_tz FROM table_name;

In this syntax:

• timestamp can be a timestamp value or a date value
• source_tz is the name of the source time zone
• target_tz is the name of the target time zone

For example, let’s say you have a timestamp value of ‘2023-03-17 12:00:00’ in the US/Pacific time zone, and you want to convert it to the US/Eastern Time Zone. Here’s how you can do it using the AT TIME ZONE function:

SELECT 
  TIMESTAMP '2023-03-17 12:00:00'  AT TIME ZONE 'US/Eastern' EU
  FROM DUAL;

The result of this query would be a timestamp value of ’17-MAR-23 07:00:00 +00:00′, which is the same moment in time as the input timestamp, but expressed in the US/Eastern time zone.

Time zone abbreviations

In Oracle, time zone abbreviations are not recommended to be used because they are ambiguous and can cause confusion. Time zone abbreviations such as EST, PST, GMT, etc. can refer to multiple time zones, and their meanings can also change depending on the context.

Instead, it is recommended to use the full time zone names, such as America/New_York, America/Los_Angeles, Europe/London, etc. These time zone names are more specific and unambiguous, and they can be used consistently across different systems and applications.

To find the list of time zones supported by Oracle, you can query the V$TIMEZONE_NAMES view. Here’s an example query:

SELECT * FROM V$TIMEZONE_NAMES;

This query will return a list of all the time zones supported by Oracle, along with their corresponding full names and abbreviations (if available). Note that not all time zones have abbreviations, and some abbreviations may be deprecated or obsolete.

In general, it is recommended to use the AT TIME ZONE function in Oracle with full time zone names instead of abbreviations. This will ensure that your queries and applications work consistently and accurately across different time zones.

Time zone offset values

In Oracle, you can use time zone offset values to represent the difference between a local time and Coordinated Universal Time (UTC), in hours and minutes. Time zone offset values are expressed as a string in the format (+/-)hh:mm, where hh is the number of hours and mm is the number of minutes.

For example, the time zone offset value for Pacific Standard Time (PST) is -08:00, which means that PST is eight hours behind UTC.

To convert a timestamp or date value to a different time zone using a time zone offset value, you can use the FROM_TZ function in combination with the AT TIME ZONE function. Here’s an example:

SELECT FROM_TZ(CAST(timestamp_column AS TIMESTAMP), '-08:00') AT TIME ZONE '+01:00'
  FROM table_name;

In this example, timestamp_column is a column that contains timestamp values in the PST time zone. The FROM_TZ function is used to convert the timestamp values to a TIMESTAMP WITH TIME ZONE data type, with a time zone offset value of -08:00 (which represents PST). The AT TIME ZONE function is then used to convert the timestamp values to a different time zone with a time zone offset value of +01:00.

Note that time zone offset values are not the same as time zone names, and they do not account for daylight saving time or other time zone adjustments. In general, it is recommended to use time zone names instead of offset values whenever possible, as they are more specific and less prone to errors.

Best practices for date formatting in Oracle

1. Use the standard date format: The standard date format in Oracle is YYYY-MM-DD, which is also known as the ISO format. This format is unambiguous and widely recognized, and it can be easily converted to other formats if needed. Using the standard date format also helps to ensure that your queries and applications work consistently across different systems and time zones.

2. Use the TO_DATE function: When converting a string value to a date value in Oracle, use the TO_DATE function instead of relying on implicit data type conversion. The TO_DATE function allows you to specify the format of the input string, which helps to avoid ambiguity and errors.

3. Avoid using date literals: When working with date values in Oracle, avoid using date literals (e.g. ’17-MAR-23′) unless it is absolutely necessary. Date literals can be affected by the NLS_DATE_FORMAT setting, which can vary across different systems and environments. Instead, use the TO_DATE function to explicitly convert date strings to date values.

4. Use the AT TIME ZONE function: When working with date and time values in different time zones, use the AT TIME ZONE function in Oracle to convert values between time zones. This function takes a timestamp or date value and a time zone name or offset value as input, and returns the value in the specified time zone.

5. Store dates in the local time zone: When storing date values in Oracle, it is recommended to store them in the local time zone of the database or application. This helps to ensure that date values are consistent across different systems and time zones, and it simplifies the process of converting date values to different time zones when needed.

Bonus: A few tricks

Return the day name

In Oracle, you can easily return individual date parts, such as the day name or the month name, by using the applicable format element in your TO_CHAR() function. For instance, to return the full day name, you can use ‘Day’ as the format element:

SELECT TO_CHAR(DATE '2025-07-25', 'Day') FROM DUAL;

This query would return ‘Friday’ as the result. Alternatively, you can use ‘Dy’ to return the short day name:

SELECT TO_CHAR(DATE '2025-07-25', 'Dy') FROM DUAL;

Return the month name

To return the month name in Oracle, you can use the ‘Month’ format element in the TO_CHAR function.

SELECT TO_CHAR(DATE '2025-07-25', 'Month')
FROM DUAL;

Similarly, you can use the ‘Mon’ format element to return the abbreviated month name:

SELECT TO_CHAR(DATE '2025-07-25', 'Mon')
FROM DUAL;

The result of this query would be:

Jul

Manage case

In the previous examples, the day and month names were capitalized. However, you can also return day and month names in all uppercase or all lowercase by using uppercase or lowercase format elements, respectively.

SELECT 
    TO_CHAR(DATE '2025-07-25', 'DY, Dy, dy')
FROM DUAL
UNION ALL
SELECT 
    TO_CHAR(DATE '2025-07-25', 'DAY, Day, day')
FROM DUAL
UNION ALL
SELECT 
    TO_CHAR(DATE '2025-07-25', 'MON, Mon, mon')
FROM DUAL
UNION ALL
SELECT 
    TO_CHAR(DATE '2025-07-25', 'MONTH, Month, month')
FROM DUAL;

Case sensitivity in date formatting is important to consider if you’re working with an application or system that expects date values to be in a specific case. For example, if you’re using a system that expects month names to be in all caps, it’s important to use the appropriate format element to ensure that your dates are formatted correctly.

Another thing to note is that the case sensitivity of date formats may vary depending on the language and territory settings of your Oracle database. Some languages may have specific conventions for capitalization in date formats, so it’s important to be aware of these conventions when formatting dates.

Conclusion

Working with date and time values in Oracle requires a good understanding of date formatting and conversion functions and a great attention to detail. If you are looking for a good tool that can help you manage and manipulate your data easily and efficiently, we highly recommend checking out dbForge Studio for Oracle, which provides a comprehensive set of features for working with Oracle databases, including advanced SQL editing, data management, and database design tools.

If you want to experience the benefits of the Studio for yourself, you can download and try it for free for 30 days. With its wide range of features and comprehensive set of tools, dbForge Studio for Oracle is the ideal choice for anyone looking to maximize their productivity and efficiency when working with Oracle databases.