Condition in where

Transact-SQL
1

Hi,

I have 4 date column is the table and need to check below condition in the Where

Where ETA > Getdate() but if ETA is Null or empty then BTA > Getdate, if BTA is Null or Empty then check ATA > getdate() then ATD > Getdate if ATD is null or empty exclude the record.

thanks in advance.

2

Dates aren't really "empty", if you load a blank value into them you get '19000101' as the date. Therefore:

WHERE 1 = CASE 
    WHEN ETA > '19000101' THEN CASE WHEN ETA > GETDATE() THEN 1 ELSE 0 END 
    WHEN BTA > '19000101' THEN CASE WHEN BTA > GETDATE() THEN 1 ELSE 0 END 
    WHEN ATA > '19000101' THEN CASE WHEN ATA > GETDATE() THEN 1 ELSE 0 END 
    WHEN ATD > '19000101' THEN CASE WHEN ATD > GETDATE() THEN 1 ELSE 0 END 
    ELSE 0 END
1 Like
3

I think COALESCE would be perfect for this:

COALESCE (Transact-SQL) - SQL Server | Microsoft Docs

WHERE COALESCE(ETA,BTA,ATA,ATD) > GetDate()

Example

DECLARE @DateNullCoalesce AS TABLE
(
	AAA tinyint,
	ETA Datetime NULL,
	BTA Datetime NULL,
	ATA Datetime NULL,
	ATD Datetime NULL
)

INSERT INTO @DateNullCoalesce
SELECT 
	1 AS AAA,
	GETDATE() AS ETA,
	DATEADD(day,1, GETDATE()) AS BTA,
	DATEADD(day,2, GETDATE()) AS ATA,
	DATEADD(day,3, GETDATE()) AS ATD
UNION
SELECT
	2 AS AAA,
	NULL AS ETA,
	DATEADD(day,1, GETDATE()) AS ATA,
	DATEADD(day,2, GETDATE()) AS ATA,
	DATEADD(day,3, GETDATE()) AS ATD
UNION
SELECT
	3 AS AAA,
	NULL AS ETA,
	NULL AS BTA,
	DATEADD(day,2, GETDATE()) AS ATA,
	DATEADD(day,3, GETDATE()) AS ATD
UNION
SELECT
	4 AS AAA,
	NULL AS ETA,
	NULL AS BTA,
	NULL AS ATA,
	DATEADD(day,3, GETDATE()) AS ATD
UNION
SELECT
	5 AS AAA,
	NULL AS ETA,
	NULL AS BTA,
	NULL AS ATA,
	NULL AS ATD

SELECT *, COALESCE(ETA,BTA,ATA,ATD) AS Result 
FROM @DateNullCoalesce
WHERE COALESCE(ETA,BTA,ATA,ATD) > GETDATE()
ORDER BY AAA
4

What about the "empty" date? Could do NULLIF, but I decided not to do that.

5

Yes, if it's empty then NULLIF should be used also.

1 Like
6

great guys, thank you for your help. I will try and update the results.