Is there a more elegant way to obtain the least of 3 dates?

[Former Member]

I need the least of 3 dates, all of which may be null. So I first Coalesce the ending parameter date with each, then have a multi-column select so I can use the intermediate columns instead of a single line column expression that would be too long for my brain to comprehend.

It all works fine, but surely there must be a simple, more elegant way to accomplish this??

    SELECT  IF empstat_date_start IS NOT NULL THEN IF empstat_date_start > empworkauth_date_start THEN empstat_date_start ELSE empworkauth_date_start ENDIF ELSE empworkauth_date_start ENDIF AS wa_date_start,
            COALESCE(empstat_date_end, ad_accrual_end_date)     AS wa_empstat_date_end,
            COALESCE(emptype_date_end, ad_accrual_end_date)     AS wa_emptype_date_end,
            COALESCE(empworkauth_date_end, ad_accrual_end_date) AS wa_empworkauth_date_end,
            IF wa_empstat_date_end < wa_emptype_date_end THEN wa_empstat_date_end ELSE wa_emptype_date_end ENDIF AS date_compare1,
            IF date_compare1 < wa_empworkauth_date_end THEN date_compare1 ELSE wa_empworkauth_date_end ENDIF AS wa_date_end,

Thanks for any suggestions. Bill

[Breck_Carter · Accepted solution]

It's not clear to me how the posted code relates to the question, so it's also not clear what should be done if any/all of the dates are null, but "the least of" requirement seems a natural for LESSER():

SELECT LESSER ( COALESCE ( dt1, '9999-12-31' ), 
                LESSER ( COALESCE ( dt2, '9999-12-31' ), 
                         COALESCE ( dt3, '9999-12-31' ) )
              );

[Former Member · Accepted solution]

LESSER. Yes, that's exactly what I was missing. That indeed will do it. It is so obvious now, but that function escaped me. Thanks!

[VolkerBarth · Accepted solution]

Just to link to a somewhat related question:)

• How to use MAX() as a scalar function?

[former_SQLA_member1694900 · Accepted solution]

Soooo, Breck's answer should be marked as the answer to this question, right?

[Former Member · Accepted solution]

yes, it should. But I can't figure out how to remove the incorrect "accepted Answer" and change it to the correct one.

[Former Member · Accepted solution]

I was going thru the same struggle trying to figure out how to make MIN() work.

[VolkerBarth · Accepted solution]

AFAIK, you will have to uncheck the current accepted answer by unckecking the according marker before you can accept the "correct one".

[Former Member · Accepted solution]

It appeared I successfully cleared the Accepted mark, but even after refreshing the page I would get a message that the answer was already accepted when I tried checking the other. Now after closing the browser, visiting a few other web sites, and trying again it let me. The magic of technology.

[Breck_Carter · Accepted solution]

Doncha wish there was a LEAST ( expression, expression [, ... ] )?

[Former Member · Accepted solution]

Yep! That seemed natural and I tried it: but a few syntax errors finally led me to look at your post and the docs more carefully.