December 10, 2014 at 8:39 am
I have two dates. How do I get the one that is the lowest. One may be null. I don't want null unless they are both null
I tried..
DECLARE @Handle date
SELECT @Handle = dbo.getTrkLeastDate('2014-12-09',NULL)
print @Handle
ALTER FUNCTION [dbo].[getTrkLeastDate] (@d1 date, @d2 date)
RETURNS datetime
AS
BEGIN
DECLARE @least date
IF @d1 is null and @d2 is null
SET @least = null
ELSE IF @d1 < @d2 or @d2 is not null
SET @least = @d1
ELSE
SET @least = @d2
RETURN @least
END
December 10, 2014 at 8:53 am
Something like?
IF ISNULL(@d1, '12/31/9999') < ISNULL(@d2, '12/31/9999')
RETURN @d1
ELSE
RETURN @d2
December 10, 2014 at 12:35 pm
Any way you slice it this isn't going to be super costly, but another spin on it would be to use aggregate functions rather than case or inequality statements. This is a paraphrasing of a function we use in a production system.
declare @d1 date = '2014-12-09', @d2 Date = '2011-08-29'
select min(theDate)
from (values(@d1), (@d2)) a (theDate)
December 10, 2014 at 1:07 pm
@jay Tee
But it's still butt ugly code 😀
It is one of the few really useful functions that is missing from T-SQL; the ability to select a MIN or MAX from an array of values. That, along with MODAL and MEDIAN averages against a dataset.
I would like to see something like the coalesce() syntax. I know that COALESCE() only consumes the array values until it find one that is not null, but I would have thought that the iteration of the collection would have similar performance issues
December 10, 2014 at 1:09 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply