Oracle Decode function results with different formats

Question

SELECT DECODE (SYSDATE, SYSDATE + 1, NULL, SYSDATE)
  FROM DUAL;


SELECT DECODE (SYSDATE, SYSDATE + 1, TO_DATE (NULL), SYSDATE)
  FROM DUAL;

why am i getting the results in different formats from the queries above?

i am using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

Answer 1

Hi mutoss,

the decode function result has the datatype of the third parameter. In the first case, since no datatype is specified for NULL, the default VARCHAR2 is used. In the second case, a DATE is explicitely asked for and therefore the result is a date.

In other words, the first query is the same as:

SELECT DECODE(SYSDATE, SYSDATE + 1, to_char(NULL), to_char(SYSDATE)) FROM DUAL;

The output of this query will be formatted as per your NLS_DATE_FORMAT session parameter, while the second query will return a date which will be displayed according to your client settings.