About

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

Articles Related

The case

First Statement : Date Format : 01/11/95

Second Statement : Date Format : 11-01-95

Where and why we have this difference ?

Why this difference in the date format ?

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.

What we obtain in SQL Plus ?

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.

What govern the date format in Toad

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

You see ?