This article contains common examples of formatting dates in Oracle Database.

The default date format for your session is determined by various NLS initialisation parameters (here’s how to check them). You can take advantage of these parameters to output locale aware formatting.

You can also use functions like TO_CHAR(number) to convert dates to a string and format them exactly as you like on the fly.

Default Date Format

When you return a date in Oracle, by default, it’s returned in the default date format for the current session.

Example:

SELECT DATE '2030-12-10' FROM DUAL;

Result:

10/DEC/30

In this case, my session’s default date format is DD/MON/RR, and so the result reflects that.

You can change the format by either changing the NLS_TERRITORY parameter (which implicitly changes other parameters such as datetime parameters), or changing the NLS_DATE_FORMAT parameter directly.

Here’s what happens when I change the NLS_TERRITORY parameter to a different territory:

ALTER SESSION SET NLS_TERRITORY = 'Germany';
SELECT DATE '2030-12-10' FROM DUAL;

Result:

10.12.30

The default date format has been updated to reflect the format for that locale.

There are also other datetime NLS parameters, such as NLS_TIME_FORMAT, NLS_TIME_TZ_FORMAT, NLS_TIMESTAMP_FORMAT, and NLS_TIMESTAMP_TZ_FORMAT that you need to be aware of when changing datetime formats.

Also see How to Change your Session’s Date Format if you want to change the default datetime format for your current session.

The TO_CHAR() Function

The TO_CHAR(datetime) function accepts a datetime value, and returns a string formatted in a way that you specify.

Here’s a quick example:

SELECT TO_CHAR(DATE '2035-09-26', 'Dy, DD Month YYYY')
FROM DUAL;

Result:

Wed, 26 September 2035

Here, the function returned the date that was provided in the first argument, in the format specified by the second argument.

The second argument provides the format model. The format model can consist of one or more format elements. For example, Dy is a format element, as is DD, Month, etc.

If you don’t specify a format, it’s returned using the default format for the session.

See this Full List of Datetime Format Elements in Oracle for a list of format elements that can be used to format datetime values with this function.

Below are more specific examples.

Return the Day Name

You can return individual date parts if required. For example, you can return just the day name, just the month name, etc. All you need to do is use the applicable format element as the only format element in your format model.

Here’s an example of returning the day name:

SELECT TO_CHAR(DATE '2035-09-26', 'Day')
FROM DUAL;

Result:

Wednesday

In this case I returned the full day name.

We can use Dy like in the earlier example to return the short day name:

SELECT TO_CHAR(DATE '2035-09-26', 'Dy')
FROM DUAL;

Result:

Wed

Return the Month Name

Here’s an example of returning the month name:

SELECT TO_CHAR(DATE '2035-09-26', 'Month')
FROM DUAL;

Result:

September

And the short month name:

SELECT TO_CHAR(DATE '2035-09-26', 'Mon')
FROM DUAL;

Result:

Sep

Case Sensitivity

In the previous examples, we capitalised the first letter for the day and month names.

We can alternatively use all upper case to return the day and month names in uppercase, and all lower case to return them in lowercase.

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

Result:

WED, Wed, wed                           
WEDNESDAY, Wednesday, wednesday         
SEP, Sep, sep                           
SEPTEMBER, September, september         

Language

The NLS_DATE_LANGUAGE parameter is used to determine the language of date parts that are spelled out (such as day names and month names). This parameter’s default value is derived from the NLS_LANGUAGE parameter.

Here’s an example of updating the NLS_DATE_LANGUAGE parameter, then returning a formatted date that includes the day name and month name:

ALTER SESSION SET NLS_DATE_LANGUAGE = 'Spanish';
SELECT TO_CHAR(DATE '2035-09-26', 'Dy, DD Month YYYY')
FROM DUAL;

Result:

Mié, 26 Septiembre 2035

The language can also be explicitly specified from within the TO_CHAR() function itself. Doing this enables you to temporarily override the default language settings for the current session, without affecting those settings.

Example:

ALTER SESSION SET NLS_DATE_LANGUAGE = 'English';
SELECT TO_CHAR(
    DATE '2035-09-26', 
    'Dy, DD Month YYYY',
    'NLS_DATE_LANGUAGE = Spanish'
    )
FROM DUAL;

Result:

Mié, 26 Septiembre 2035

In this example, I set my current session to use English, then ran a query to output a date using Spanish.

As previously alluded to, outputting the date in Spanish did not affect the English setting. To demonstrate this, I ran the following query immediately after that one (without changing the NLS_DATE_LANGUAGE parameter).

SELECT TO_CHAR(
    DATE '2035-09-26', 
    'Dy, DD Month YYYY'
    )
FROM DUAL;

Result:

Wed, 26 September 2035

As expected, the result is in English, which is the default language for the current session.