Toad - The date format with null and decode

About

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

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.

[email protected]>show parameters NLS_DATE_FORMAT

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

[email protected]>SELECT value FROM nls_session_parameters WHERE parameter='NLS_DATE_FORMAT';

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

[email protected]>select decode(0,0,day, NULL) from d_time
  2  where day is not null
  3  and rownum = 1;

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

[email protected]>select decode(0,1,NULL,day) from d_time
  2  where day is not null
  3  and rownum = 1;

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

[email protected]>

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 ?

_


Powered by ComboStrap