Analytics
Enthusiast

## how to find earliest date from null and non null dates

Problem - There are 4 dates for each customer. One or more of these dates can be null. We need to find earliest non null date from among these. I tried LEAST function but that just puts null date as the earliest date. Iused this code but that won't work.,

,CAST(LEAST
(
CAST(COALESCE(DT1, DATE '1900-01-01') AS INT),
CAST(COALESCE(DT2, DATE '1900-01-01') AS INT),
CAST(COALESCE(DT3, DATE '1900-01-01') AS INT),
CAST(COALESCE(DT4 '1900-01-01') AS INT)
)AS DATE) AS leastdate

Accepted Solutions
Supporter

## Re: how to find earliest date from null and non null dates

Hi Dnoeth,

I miss-understood the requirement. I got it now.

Thanks,
Rohan Sawant

1 ACCEPTED SOLUTION
6 REPLIES
Junior Contributor

## Re: how to find earliest date from null and non null dates

Come on, that's too easy :-)

Use a high date instead of 1900:

`COALESCE(DT1, DATE '9999-12-31')`

And if all columns might be NULL you need to add a final NULLIF:

``` ,NULLIF(CAST(LEAST
(
CAST(COALESCE(DT1, DATE '9999-12-31') AS INT),
CAST(COALESCE(DT2, DATE '9999-12-31') AS INT),
CAST(COALESCE(DT3, DATE '9999-12-31') AS INT),
CAST(COALESCE(DT4, DATE '9999-12-31') AS INT)
)AS DATE), DATE '9999-12-31') AS leastdate```

Supporter

## Re: how to find earliest date from null and non null dates

Hi dnoeth ,

May be I am missing something but I don't understand how changing the date will change the outcome. How can NULL be a outcome with coalesce applied at all cases. Can you please help me in understanding it.

Thanks,

Rohan Sawant

Junior Contributor

## Re: how to find earliest date from null and non null dates

When all input columns are NULL the LEAST will return '9999-12-31', but the actual result should be NULL.

## Re: how to find earliest date from null and non null dates

You want the least of 4 dates, but you don't want Null to show up as the least.  Therefore you have to treat Null as it were the highest date.  That's what this part does:

LEAST (
CAST(COALESCE(DT1, DATE '9999-12-31') AS INT),
CAST(COALESCE(DT2, DATE '9999-12-31') AS INT),
CAST(COALESCE(DT3, DATE '9999-12-31') AS INT),
CAST(COALESCE(DT4, DATE '9999-12-31') AS INT) )

But if all the dates are Null, you probably Do want Null as the result.  That's what this does:

NULLIF( CAST( Least( ... ) AS DATE), DATE '9999-12-31')

In other words, if the result of the Least() function is 9999-12-31, then this returns Null.  That's how you know all 4 dates were null.

Supporter

## Re: how to find earliest date from null and non null dates

Hi Dnoeth,

I miss-understood the requirement. I got it now.

Thanks,
Rohan Sawant

Enthusiast

## Re: how to find earliest date from null and non null dates

Thanks dnoeth! That was indeed too easy, thanks for the solution.