July 22, 2009 at 4:04 am
Hi all,
I'm getting in a spin trying to code a gracious CASE statement and would appreciate some advice.
I have 2 date fields to compare and want to return the following:-
if date1 and date2 are both present return the mininum of the two.
if date1 is null and date2 are null return null.
if date1 is null but date2 is present return date2
if date1 is present but date2 is null return date1
would this be best achieved using CASE ?
Many thanks in advance.
WW
July 22, 2009 at 5:02 am
CASE
WHEN COALESCE(date2, 0) < COALESCE(date1, 0)
THEN date2
ELSE COALESCE(date1, date2)
END
July 22, 2009 at 5:05 am
The last three requirements,
if date1 is null and date2 are null return null.
if date1 is null but date2 is present return date2
if date1 is present but date2 is null return date1
can be expressed like this: coalesce(@date1, @date2)
(coalesce is actually shorthand for a 'disguised' case)
The first requirement would probably be easiest to spell out with a case statement.
if date1 and date2 are both present return the mininum of the two.
So, all in all, something like this seems to work...
declare @date1 datetime, @date2 datetime
select @date1 = '20090201', @date2 = '20090201'
select case when @date1 is not null and @date2 is not null
then case when @date1 < @date2
then @date1
else @date2
end
else coalesce(@date1, @date2)
end
/Kenneth
July 22, 2009 at 5:21 am
Thanks guys, thats a real help and certainly both are better than my efforts!
July 22, 2009 at 5:59 am
Doesnt work when date1 is not null and date2 is null
Ken McKelvey (7/22/2009)
CASE
WHEN COALESCE(date2, 0) < COALESCE(date1, 0)
THEN date2
ELSE COALESCE(date1, date2)
END
CASE
WHEN @d1 < COALESCE(@d2,'20991231') THEN @d1
ELSE @d2
END
July 22, 2009 at 6:04 am
johan.brohn (7/22/2009)
Doesnt work when date1 is not null and date2 is nullKen McKelvey (7/22/2009)
CASE
WHEN COALESCE(date2, 0) < COALESCE(date1, 0)
THEN date2
ELSE COALESCE(date1, date2)
END
CASE
WHEN @d1 < COALESCE(@d2,'20991231') THEN @d1
ELSE @d2
END
Actually it does:
select
case
when @date1 is not null
and @date2 is not null
then case when @date1 < @date2 -- @date1 and @date2 are not null, do the THEN
then @date1
else @date2
end
else coalesce(@date1, @date2) -- else one of them is null
end
With this: coalesce(@date1, @date2) , if @date1 is not null and @date2 is null, @date1 is returned. If @date1 is null and @date2 is ot, @date2 is returned. If both are null you get null.
July 22, 2009 at 6:11 am
Yes, Kenneth's works but Ken McKelvey's (the one I quoted) doesnt
July 22, 2009 at 6:21 am
johan.brohn (7/22/2009)
Yes, Kenneth's works but Ken McKelvey's (the one I quoted) doesnt
Sorry, still half a sleep. Need more caffine.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply