Toad - The date format with null and decode

When you use Toad with a date and a decode statement, you can have the surprise that two different date format appear.

First Statement : Date Format : 01/11/95

Second Statement : Date Format : 11-01-95

Where and why we have this difference ?

Because when you use a decode statement, Toad (of Oracle) return as type the type of the first argument from the decode function.

In the first case, a NULL value is the first returned and NULL have a data type of varchar. In the second case, the column day with the data type of date is returned.

The date format returned by Oracle is governed by the parameters NLS_DATE_FORMAT.

hr@orcl>show parameters NLS_DATE_FORMAT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string

hr@orcl>SELECT value FROM nls_session_parameters WHERE parameter='NLS_DATE_FORMAT';

VALUE
---------
DD-MON-RR

hr@orcl>select decode(0,0,day, NULL) from d_time
  2  where day is not null
  3  and rownum = 1;

DECODE(0,
---------
11-JAN-95

hr@orcl>select decode(0,1,NULL,day) from d_time
  2  where day is not null
  3  and rownum = 1;

DECODE(0,
---------
11-JAN-95

hr@orcl>

The same date format. So, the formatting occurs in Toad.

Go to View > Toad Option > Data Grids > Data.

You see ?