Select earliest of two nullable datetimes

Question

In a table with two nullable date/datetime columns, how would you efficiently select the earliest of both dates that's not NULL?

Concretely, this is what I'm interested in:

|   Date1    |   Date2    |    |  Expected  |
|------------|------------|    |------------|
| 2014-02-23 | 2014-01-16 |    | 2014-01-16 |
| 2014-02-01 |    NULL    |    | 2014-02-01 |
|    NULL    | 2014-01-13 |    | 2014-01-13 |
|    NULL    |    NULL    |    |    NULL    |

I can manage to fetch those results using either of the queries below. Both my solutions use the LEAST operator, which returns NULL when either of the arguments is NULL (and not just when both are NULL), so it takes some more logic to get the job done.

SELECT IF(ISNULL(Date1), Date2, IFNULL(LEAST(Date1, Date2), Date1)) AS EarlyDate FROM MyTable

or the equivalent:

SELECT IFNULL(IFNULL(LEAST(Date1, Date2), Date1), Date2) AS EarlyDate FROM MyTable

My feeling is that there's something redundant in both of my queries above. Could anyone suggest a better solution?

Answer 1

You have to check for NULLs, try this:

SELECT IF(Date1 IS NULL OR Date2 IS NULL, 
          COALESCE(DATE1, DATE2), 
          LEAST(DATE1, DATE2)
       ) AS EarlyDate 
FROM MyTable

Working Fiddle: http://sqlfiddle.com/#!2/7940c/1

Answer 2

You can use LEAST on COALESCE(date1,date2) and COALESCE(date2,date1):

SELECT LEAST( COALESCE(Date1,Date2) , COALESCE(Date2,Date1) ) AS EarlyDate
FROM MyTable;