asked on

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

Vitor Montalvão

Let us know what you want to do when a NULL is found.

Mark Wilson

ASKER

Hi

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 Sontakke

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
go

set quoted_identifier on
go

create function [dbo].[greatest] (@date1 datetime, @date2 datetime)
returns datetime
begin
   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;
end;
go

-- 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 https://www.sqlservercentral.com/Forums/489858/GREATEST-and-LEAST-function and modified.

Peter Chan

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 Sontakke

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ão

Try this:

CASE 
	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 
END

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wilson

ASKER

Thanks worked well

Nitin Sontakke

@Scott Pletcher - Yes, lot better from performance point of view.