Use something like LEAST in T-SQL on a datetime field

Question

Possible Duplicate:
Getting the minimum of two values in sql

Okay what I Have a table with two datetime fields and I want to select the rows where the oldest date is equal to some date variable. I saw the LEAST function used somewhere but I can't use this in T-SQL

I need something like this

SELECT LEAST(date1, date2) as theDate FROM theTable WHERE theDate = '2012-09-24'

but that will work in T-SQL. Also date1 or date2 can sometimes be null so that may be important to know.

Already answered see this post: http://stackoverflow.com/questions/1947753/getting-the-minimum-of-two-values-in-sql — Michael, Sep 24 '12 at 17:10
The Oracle LEAST function will return the least of two *or more* values. I came here searching for an alternative to Oracle's LEAST, and all I find here is how to get the least of two values. I'm going to have to look elsewhere for a solution. — Baodad, Jan 24 '17 at 21:35

score 7 · Answer 1 · answered Sep 24 '12 at 17:23

7

The CASE solution others have provided works well for the case (no pun intended) of two values. For future reference, it becomes a bit unweildy for three or more values and you might then want to do something like this:

SELECT (SELECT MIN(mydate) 
        FROM (VALUES(date1),(date2),(date3)) mylist(mydate)
       ) AS theDate
FROM theTable 
WHERE theDate = '2012-09-24'

answered Sep 24 '12 at 17:23

GilM

3,711
17
18

1

Interesting, but requires SQL 2008 or higher. The OP did not specify. See http://msdn.microsoft.com/en-us/library/dd776382.aspx – Matt Johnson-Pint Sep 24 '12 at 21:51
Agreed with @Matt but, the concept is important, not the syntax. If the OP is using a specific legacy version, they should say so. Easy enough to re-write this using union to cater for that. – Aaron Bertrand Sep 24 '12 at 22:15
Okay, if you need to reference it in the WHERE clause, put the first select in a CTE or a CROSS APPLY. – GilM Mar 08 '16 at 22:18
`SELECT MIN(dt) FROM (VALUES((SELECT max(CreateDate) FROM Customers (NOLOCK))), ((SELECT max(CreateDate) FROM LinkedServ.x.dbo.Customers))) dtlist(dt);` – beloblotskiy May 03 '16 at 17:33

score 4 · Accepted Answer · answered Sep 24 '12 at 17:05

There is no such function in T-SQL. Try:

SELECT theDate = CASE WHEN date1 < date2 THEN date1 ELSE date2 END FROM ... ;

To handle NULLs you may want:

SELECT theDate = NULLIF(CASE WHEN date1 < date2 THEN date1 ELSE date2 END, '20301231')
FROM 
(
  SELECT 
    date1 = COALESCE(date1, '20301231'), 
    date2 = COALESCE(date2, '20301231')
  FROM ...
) AS x;

Also, you can't reference the alias theDate in the WHERE clause, it doesn't exist yet. You might want to say:

WHERE '20120924' IN (date1, date2);

score 1 · Answer 3 · answered Sep 24 '12 at 17:05

You could use a case to mimic least, and a subquery so you can refer to the case result in the where clause:

select  *
from    (
        select  case 
                when date1 > date2 or date1 is null then date2
                else date1
                end as theDate
        ,       *
        from    TheTable
        )
where   theDate = '2012-09-24'

score 1 · Answer 4 · answered Sep 24 '12 at 17:25

While the other answers are indeed valid, here it is in the form of a UDF, which is closer to what you asked for:

CREATE FUNCTION [dbo].[LeastDate] (@d1 datetime, @d2 datetime)
RETURNS datetime
AS
BEGIN
    DECLARE @least datetime

    IF @d1 is null or @d2 is null
        SET @least = null
    ELSE IF @d1 < @d2
        SET @least = @d1
    ELSE
        SET @least = @d2

    RETURN @least
END

Usage:

SELECT dbo.LeastDate(date1, date2) as theDate
FROM theTable
WHERE dbo.LeastDate(date1, date2) = '2012-09-24'

Or you can use a nested select to do it one time only:

SELECT * FROM (
  SELECT dbo.LeastDate(date1, date2) as theDate FROM theTable
) t
WHERE theDate = '2012-09-24'

Note that the choice of calling the function separately in the where clause or using a nested select is the same as it would be for any SQL function, not just this one.

So you're going to call a multi-statement scalar function twice for every row? Ouch. — Aaron Bertrand, Sep 24 '12 at 18:10
The OP didn't give any mention to the size of the data, but I suppose you are right - as a general purpose function this would be rather slow on large data. It could be optimized to a table valued udf (http://www.sqlmag.com/article/sql-server/inline-scalar-functions) but then you might as well just do it inline anyway since there's so much involved in calling it. — Matt Johnson-Pint, Sep 24 '12 at 22:04

Use something like LEAST in T-SQL on a datetime field

4 Answers4