What is the purpose of using `timestamp(nullif(”,”))`

Folks

I am in the process of moving a decade old back-end from DB2 9.5 to Oracle 19c.

I frequently see in SQL queries and veiw definitions bizarre timestamp(nullif('','')) constructs used instead of a plain null.

What is the point of doing so? Why would anyone in their same mind would want to do so?

Disclaimer: my SQL skills are fairly mediocre. I might well miss something obvious.

Answer

It appears to create a NULL value with a TIMESTAMP data type.

The TIMESTAMP DB2 documentation states:

TIMESTAMP scalar function

The TIMESTAMP function returns a timestamp from a value or a pair of values.

TIMESTAMP(expression1, [expression2])

expression1 and expression2

The rules for the arguments depend on whether expression2 is specified and the data type of expression2.

• If only one argument is specified it must be an expression that returns a value of one of the following built-in data types: a DATE, a TIMESTAMP, or a character string that is not a CLOB.

If you try to pass an untyped NULL to the TIMESTAMP function:

TIMESTAMP(NULL)

Then you get the error:

The invocation of routine "TIMESTAMP" is ambiguous. The argument in position "1" does not have a best fit.

To invoke the function, you need to pass one of the required DATE, TIMESTAMP or a non-CLOB string to the function which means that you need to coerce the NULL to have one of those types.

This could be:

TIMESTAMP(CAST(NULL AS VARCHAR(14)))
TIMESTAMP(NULLIF('',''))

Using NULLIF is more confusing but, if I have to try to make an excuse for using it, is slightly less to type than casting a NULL to a string.

---

The equivalent in Oracle would be:

CAST(NULL AS TIMESTAMP)

This also works in DB2 (and is even less to type).