SQL Find min from two datetime values ​​a, b when b can be null

Question

I am writing an expression in a Transact SQL query to find the minimum record for two dates a, b, where a cannot be null, but b can be null (in this case, return a).

I have the following that I think is right and can even be effective, but certainly ugly.

Can we do better?

case when b is null then a else (case when b < a then b else a end) end as min_datetime

Answer 1

In SQL Server, you can do this with a side join. The correct syntax is:

select t.*, v.min_dte
from t cross apply
     (select min(v.dte) as min_dte
      from values ( (t.a), (t.b) ) v(dte)
     ) v;

This is very convenient as the number of values ​​increases. However, performance is likely to be slightly worse (but not much worse) than a single expression.

As for one expression, I would go for:

case when b is null or a < b then a else b end as min_datetime

, . .

Answer 2

ISNULL .

CASE WHEN A < ISNULL(B, '2099-01-01') THEN A ELSE B END AS min_datetime

Answer 3

SELECT COALESCE(IIF(@a > @b, @b, @a), @a, @b ) as 'Minimum date'

IIF (@a> @b, @b, @a) will return the minimum date between the two. If one of the dates is null, it will return null.

COALESCE will take care of returning the first non-zero value.

Answer 4

SELECT MIN(Val)
FROM Table  a
CROSS APPLY
(
  VALUES (a.a),(a.b)
) x(Val)

Answer 5

Try the following:

SELECT CASE WHEN ISNULL(b, '1900-01-01')> a THEN ISNULL(b, '1900-01-01') ELSE a END

Answer 6

I think it will do it

min(isnull(b,a))