Comparing DateTime Values

SQL 2008

I have three datetime fields I am comparing in a CASE statement below. The problem I am having is with NULL values. If one field has a datetime value in it and the other 2 have nulls then the result is NULL. How can I avoid this. Thanks

    CASE WHEN dy.LastCall > emaildate
                 AND dy.LastCall > crystalcldate
                 THEN dy.LastCall
            WHEN emaildate > crystalcldate
                 AND emaildate > dy.LastCall
                 THEN emaildate
            WHEN crystalcldate > dy.LastCall
                 AND crystalcldate > z.emaildate
                 THEN cm.crystalcldate END crystalcldate
Mark WilsonBI DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Let us know what you want to do when a NULL is found.
Mark WilsonBI DeveloperAuthor Commented:

If for instance all dates are null then null is returned

If LastCall has a datetime in it and emaildate and crystalcldate are null then it will show LastCall datetime
Nitin SontakkeDeveloperCommented:
DISCLAIMER FIRST: This is workable solution, only if, you are not talking about millions of rows in a table. Personally, I haven't obviously tested, however, SQL Server UDFs are known to be performance degraders.

Here is a proposed solution. Not quite a great one and personally, I am not satisfied with the code in the function. However, this looks concise.

set ansi_nulls on

set quoted_identifier on

create function [dbo].[greatest] (@date1 datetime, @date2 datetime)
returns datetime
   if (@date1 is null and @date2 is null) return null;
   if (@date1 is null and @date2 is not null) return @date2;
   if (@date1 is not null and @date2 is null) return @date1;
   return case when @date1 <= @date2 then @date2 else @date1 end;

-- Unit testing of the function...

declare @date01 date = null, @date02 date = null, @date03 date = null;                    -- 0 0 0 = 0
select [dbo].[greatest](@date01, [dbo].[greatest](@date02, @date03)) -- null

declare @date04 date = null, @date05 date = null, @date06 date = '20170901';              -- 0 0 1 = 1
select [dbo].[greatest](@date04, [dbo].[greatest](@date05, @date06)) -- 2017-09-01 00:00:00.000

declare @date07 date = null, @date08 date = '20170501', @date09 date = null;              -- 0 1 0 = 2
select [dbo].[greatest](@date07, [dbo].[greatest](@date08, @date09)) -- 2017-05-01 00:00:00.000

declare @date10 date = null, @date11 date = '20170501', @date12 date = '20170901';        -- 0 1 1 = 3
select [dbo].[greatest](@date10, [dbo].[greatest](@date11, @date12)) -- 2017-09-01 00:00:00.000

declare @date13 date = '20170101', @date14 date = null, @date15 date = null;              -- 1 0 0 = 4
select [dbo].[greatest](@date13, [dbo].[greatest](@date14, @date15)) -- 2017-01-01 00:00:00.000

declare @date16 date = '20170101', @date17 date = null, @date18 date = '20170901';        -- 1 0 1 = 5
select [dbo].[greatest](@date16, [dbo].[greatest](@date17, @date18)) -- 2017-09-01 00:00:00.000

declare @date19 date = '20170101', @date20 date = '20170501', @date21 date = null;        -- 1 1 0 = 6
select [dbo].[greatest](@date19, [dbo].[greatest](@date20, @date21)) -- 2017-05-01 00:00:00.000

declare @date22 date = '20170101', @date23 date = '20170501', @date24 date = '20170901';  -- 1 1 1 = 7
select [dbo].[greatest](@date22, [dbo].[greatest](@date23, @date24)) -- 2017-09-01 00:00:00.000

Open in new window

The code copied from and modified.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

HuaMin ChenProblem resolverCommented:
Do like

CASE WHEN isnull(dy.LastCall,convert(datetime,'20180601',112)) > isnull(emaildate,convert(datetime,'19600601',112))

Open in new window

to each date column to avoid the NULL case!
Nitin SontakkeDeveloperCommented:
If I steal HuaMinChen's idea the complete statement would look like something as follows:

 nullif(CASE WHEN isnull(dy.LastCall, '19000101') > isnull(emaildate, '19000101')
                 AND isnull(dy.LastCall, '19000101' > isnull(crystalcldate, '19000101')
                 THEN dy.LastCall
            WHEN isnull(emaildate, '19000101') > isnull(crystalcldate, '19000101')
                 AND isnull(emaildate, '19000101') > isnull(dy.LastCall, '19000101')
                 THEN emaildate
            WHEN isnull(crystalcldate, '19000101') > isnull(dy.LastCall, '19000101')
                 AND isnull(crystalcldate, '19000101') > isnull(z.emaildate, '19000101')
                 THEN cm.crystalcldate END, '19000101') crystalcldate

Open in new window

Obviously, syntax is not checked...modify as appropriate.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
	WHEN dy.LastCall IS NULL AND emaildate IS NULL AND crystalcldate IS NULL THEN NULL
	WHEN dy.LastCall > ISNULL(emaildate,dy.LastCall) AND dy.LastCall > ISNULL(crystalcldate, dy.LastCall) THEN dy.LastCall
	WHEN emaildate > ISNULL(crystalcldate, emaildate) AND emaildate > ISNULL(dy.LastCall, emaildate) THEN emaildate
	WHEN crystalcldate > ISNULL(dy.LastCall, crystalcldate) AND crystalcldate > ISNULL(z.emaildate, crystalcldate) THEN cm.crystalcldate 

Open in new window

Scott PletcherSenior DBACommented:
Let SQL do the sorting; this will also be much, much easier if/when you have more than 3 dates to check:

SELECT ca1.crystalcldate
FROM ...
    SELECT TOP (1) dates.crystalcldate
    FROM (
        VALUES(dy.LastCall),(emaildate),(cm.crystalcldate) /*,(date4),(date5),...*/
    ) AS dates(crystalcldate)
    ORDER BY dates.crystalcldate DESC
) AS ca1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WilsonBI DeveloperAuthor Commented:
Thanks worked well
Nitin SontakkeDeveloperCommented:
@Scott Pletcher - Yes, lot better from performance point of view.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.