How to get the minimum date of several columns, excluding nulls?

+2

insert into table (col1, col2, col3, col4) values ('2015-01-01','2014-01-01','2013-01-01', NULL )

I am looking for a function like this:

select least_date(col1, col2, col3, col4) from table

result: '2013-01-01'

How can I get the minimum date of several columns, excluding nulls?

You could do this:

select least(nvl(col1,'9999-12-31'), nvl(col2,'9999-12-31'), nvl(col3,'9999-12-31'), nvl(col4,'9999-12-31')) from  mytable;

assuming that you'll have some valid dates in the columns.

If you'd like it to be a little bit more natural to write, you could create a UDF that ignores the NULLs and computes the minimum for each row.

--Sharon

Sign up