Skip to content

Toad NLS_DATE_FORMAT

How do I Change NLS Date Format in Toad ?

Just like we said previously, NLS_DATE_FORMAT can be set at session, database or OS level. It depends on what you want to do.

GUI tools like Toad for Oracle usually override NLS_DATE_FORMAT at any level once you connected to the database in it.

Since Toad for Oracle has 2 types of output, one is script output, the other is data grid. We have to customize NLS_DATE_FORMAT separately.

  1. Script Output
  2. Data Grid

Script Output

We may change NLS_DATE_FORMAT at session time for script output.

Current Format

Let's see the current format before changing it.

Toad for Oracle - Before Changing NLS_DATE_FORMAT
Toad for Oracle - Before Changing NLS_DATE_FORMAT

Alter Session Set NLS_DATE_FORMAT

We execute ALTER SESSION SET NLS_DATE_FORMAT statement in SQL editor to change it, just like we said in NLS_DATE_FORMAT, How and Why, as simple as that.

Toad for Oracle - NLS_DATE_FORMAT Changed
Toad for Oracle - NLS_DATE_FORMAT Changed

Data Grid

To set NLS_DATE_FORMAT in data grid, we need to change it in the "Toad Options" of the tool.

Toad Options

Here we navigate the function menu to "Toad Options".

Toad for Oracle - View - Toad Options
Toad for Oracle - View - Toad Options

Date Time Format

We go for "Data Grids" -> "Data" and select ... next to "Date format".

Toad for Oracle - Data Grid - Date format
Toad for Oracle - Data Grid - Date format

We choose the date format we want.

Toad for Oracle - Data Grid - Change Date format
Toad for Oracle - Data Grid - Change Date format

We choose the time format we want from the drop-down menu.

Toad for Oracle - Data Grid - Change Time format
Toad for Oracle - Data Grid - Change Time format

Please click on "Apply" button to make it take effect immediately.

Test

After you apply and close the windows, the new datetime format takes effect immediately, so you don't have to logout / login.

Let's check the result.

Toad for Oracle - Data Grid - Datetime Format Changed
Toad for Oracle - Data Grid - Datetime Format Changed

We have customized NLS_DATE_FORMAT in data grid permanently.

2 thoughts on “Toad NLS_DATE_FORMAT”

  1. that wont work for me , I was using older version, today I installed newer version(16.3.231). I changed the date format from options like in the screenshots, also checked it from toad.ini file. but still it works like;
    NLS_DATE_FORMAT DD-MON-RR

Leave a Reply

Your email address will not be published. Required fields are marked *