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

David_P_1

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?

 

Sharon_Cutter

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