The result of Oracle decoding function with various 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 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

decodethe result of the function is the data type of the third parameter. In the first case, since NULLno data type is specified, VARCHAR2 is used by default. In the second case, a DATE query is specified, so the result is a date.

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

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

NLS_DATE_FORMAT, , .