Skip to content

Hive NVL does not work with Date type of the column – NullpointerException

I am using MapR Hive distribution over HDFS and facing below issue. If for a table the column type is ‘Date’ type, then the NVL function does not work. The same is working for other datatype.

It simply throws


Even explain function is throwing same exception.

Kindly help here. Is it a bug in Hive distribution?



I solved the problem myself with a workaround:

For Date type, you could use hive COALESCE function as below:

COALESCE(nt.assess_dt, cast('9999-01-01' as date))

The above answer can be explain as:

  • Find nt.assess_dt (which is a date type column); if it is null get the next value in the COALESCE function; which is a non-null value in above example and hence will be returned back.

Please note that it is a little different than NVL, where the value returned by the COALESCE needs to be of same type. Hence a blank '' can not be returned by COALESCE in the above example.

Due to this, I have used a very large date value 9999-01-01 to represent a null value and distinguish between a genuine date value. In case your date column can have indeed this large value as a valid value, you should think of some other valid date value to represent a null date.

Get more about this and other alternative here

3 People found this is helpful