Selecting most recent date between two columns
CASE is IMHO your best option:
SELECT ID, CASE WHEN Date1 > Date2 THEN Date1 ELSE Date2 END AS MostRecentDateFROM TableIf one of the columns is nullable just need to enclose in COALESCE:
.. COALESCE(Date1, '1/1/1973') > COALESCE(Date2, '1/1/1973')
select ID, casewhen Date1 > Date2 then Date1else Date2end as MostRecentDatefrom MyTable
You can throw this into a scalar function, which makes handling nulls a little easier. Obviously it isn't going to be any faster than the inline case statement.
ALTER FUNCTION [fnGetMaxDateTime] ( @dtDate1 DATETIME, @dtDate2 DATETIME) RETURNS DATETIME ASBEGIN DECLARE @dtReturn DATETIME; -- If either are NULL, then return NULL as cannot be determined. IF (@dtDate1 IS NULL) OR (@dtDate2 IS NULL) SET @dtReturn = NULL; IF (@dtDate1 > @dtDate2) SET @dtReturn = @dtDate1; ELSE SET @dtReturn = @dtDate2; RETURN @dtReturn;END