Toad - The date format with null and decode

Card Puncher Data Processing


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
Toad Decode Format Date 1

Second Statement : Date Format : 11-01-95
Toad Decode Format Date 2

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';


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


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



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 ?

Toad Option Date Format

Discover More
Obiee Filter On Date
OBIEE - Date Format in presentation variable, dashboard prompt and logical SQL

This article talk the manipulation of a presentation variable with a date datatype. Starting with OBIEE and higher versions, Dashboard Prompt input formats and presentation variable values...
Card Puncher Data Processing
Toad for Oracle

Toad is an ide to develop on the Oracle Database. During his installation, be aware that you can have this service BMFMySQL which launch this mysqld-max-nt.exe program. It's a database so a lot of memory....

Share this page:
Follow us:
Task Runner