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

 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 asked 03 Mar '14, 13:29 Bill Aumen 2.1k●34●46●75 accept rate: 16%

 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' ) ) ); ``` answered 03 Mar '14, 19:57 Breck Carter 27.1k●456●622●894 accept rate: 21% 1 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! (03 Mar '14, 20:04) Bill Aumen Replies hidden Just to link to a somewhat related question:) (04 Mar '14, 03:09) Volker Barth Soooo, Breck's answer should be marked as the answer to this question, right? (04 Mar '14, 06:06) tzup yes, it should. But I can't figure out how to remove the incorrect "accepted Answer" and change it to the correct one. (04 Mar '14, 10:25) Bill Aumen I was going thru the same struggle trying to figure out how to make MIN() work. (04 Mar '14, 10:27) Bill Aumen AFAIK, you will have to uncheck the current accepted answer by unckecking the according marker before you can accept the "correct one". (04 Mar '14, 10:32) Volker Barth 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. (04 Mar '14, 12:02) Bill Aumen 2 Doncha wish there was a LEAST ( expression, expression [, ... ] )? (04 Mar '14, 14:49) Breck Carter 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. (05 Mar '14, 14:02) Bill Aumen showing 2 of 9 show all flat view
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×52
×1

question asked: 03 Mar '14, 13:29

question was seen: 780 times

last updated: 05 Mar '14, 14:02