Skip to Main Content
  • Questions
  • How to find the minimum date between 2 date columns in the same record

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: November 21, 2005 - 1:12 pm UTC

Last updated: November 21, 2005 - 6:27 pm UTC

Version: 9i

Viewed 1000+ times

You Asked

My oracle table defenition is
id number(5)
name varchar2(30)
date1 date,
date2 date

Could anyone advice me on how can I select rows like this using a single sql statement?
emp_id, emp_name, MIN(date1,date2)

Thanks in advance.

and Tom said...

use least


select emp_id, emp_name, least(date1,date2) from ...

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What about nulls?

Jimbo, November 22, 2005 - 4:36 am UTC

The answer depends on whether you need to handle NULLs. The MIN of a set containing a NULL will be NULL only if all values are NULL. The LEAST of a pair containing a NULL will be NULL if either or both of the pair are NULL.

select emp_id, emp_name,
Least(Nvl2(date1,Nvl(date1,To_Date('01014000','DDMMYYYY')),date2),
Nvl2(date2,Nvl(date2,To_Date('01014000','DDMMYYYY')),date1))

(I'm assuming the system will be replaced before any employee records encompass the year 4000)


whoops!

Jimbo, November 22, 2005 - 4:39 am UTC

that would be

SELECT Least(Nvl2(date1,date1,date2),
Nvl2(date2,date2,date1))