CODEWITHSUNDEEP
sqlt-sql
Rob B
Rob B

Reputation: 656

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

I'm writing an expression in a Transact SQL query to find the per-record minimum of two datetimes a,b where a cannot be null but b can be null (in which case return a).

I have the following, which I think is correct, and might even be efficient, but is 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

Upvotes: 5

Views: 8177

Answers (6)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

In SQL Server, you can do this with a lateral 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 handy as the number of values gets larger. However, the performance is likely to be slightly worse (but not much worse) than a single expression.

As for a single expression, I would go for:

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

The performance of this should be essentially the same as your expression. I find this version simpler.

Upvotes: 4

Harshvardhan Chittora
Harshvardhan Chittora

Reputation: 147

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

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

COALESCE will take care of returning the first not null value.

Upvotes: 1

EzLo
EzLo

Reputation: 14189

You can use an ISNULL to force a default maximum value.

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

Upvotes: 3

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

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

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

I think this will do it 

min(isnull(b,a))

Upvotes: -1

DineshDB
DineshDB

Reputation: 6193

Try this:

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

Upvotes: 0

Related Questions