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.
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'
SELECT MIN(dt) FROM (VALUES((SELECT max(CreateDate) FROM Customers (NOLOCK))), ((SELECT max(CreateDate) FROM LinkedServ.x.dbo.Customers))) dtlist(dt);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);
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'
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.
