CODEWITHSUNDEEP
sql-serverdatetimemin
Conrad Clark
Conrad Clark

Reputation: 4526

Get the minimum value between several columns

I'm using SQL Server 2008;

Suppose I have a table 'X' with columns 'Date1', 'Date2', 'Dateblah', all of type DateTime.

I want to select the min value between the three columns, for example (simplified, with date mm/dd/yyyy)

ID       Date1          Date2           Dateblah
0     09/29/2011      09/20/2011       09/01/2011 
1     01/01/2011      01/05/2011       03/03/2010

ID    MinDate
0    09/01/2011
1    03/03/2010

Is there a bread and butter command to do that ?

Thanks in advance.

EDIT: I've seen this question What's the best way to select the minimum value from several columns? but unfortunately it won't suit me as I'm being obligated to do it against normalization because I'm making tfs work item reports, and the 'brute-force' case thing will end up being a pain if I have 6 ou 7 columns.

Upvotes: 7

Views: 15926

Answers (4)

Javier Lisandro
Javier Lisandro

Reputation: 1

Simply lets say the table where your dates are is called sells and it has two date fields Date1 and Date2 from which you want the minimum.

SELECT ( 
   SELECT MIN([x]) 
   FROM (VALUES(Date1),(Date2)) x([x])
) as minimum
FROM sells

Upvotes: 0

Israel
Israel

Reputation: 91

based on scalar function (from Tom Hunter):

SELECT ID, (SELECT MIN([date]) FROM (VALUES(Date1),(Date2),(Dateblah)) x([date])) MinDate
FROM TableName

Upvotes: 9

Tom Hunter
Tom Hunter

Reputation: 5918

Implementing a scalar function:

CREATE FUNCTION [dbo].[MIN](@a SQL_VARIANT, @b SQL_VARIANT)
RETURNS SQL_VARIANT
AS 
BEGIN
    RETURN (
        SELECT MIN([x])
        FROM (VALUES(@a),(@b)) x([x])
    )   
END
GO

DECLARE @a DATETIME = '12 JUL 2011', @b DATETIME = '20 AUG 2011'
SELECT [dbo].[MIN](@a, @b)

DECLARE @c INT = 12, @d INT = 32
SELECT [dbo].[MIN](@c, @d)

Upvotes: 1

Tom Hunter
Tom Hunter

Reputation: 5918

There is no built in function to return the min/max of two (or more) columns. You could implement your own scalar function to do this.

In SQL Server 2005+ you could use UNPIVOT to turn the columns into rows and then use the MIN function:

CREATE TABLE [X]
(
    [ID] INT,
    [Date1] DATETIME,
    [Date2] DATETIME,
    [Date3] DATETIME
)

INSERT  [X]
VALUES  (0, '09/29/2011', '09/20/2011', '09/01/2011'),
        (1, '01/01/2011', '01/05/2011', '03/03/2010')


SELECT [ID], MIN([Date]) AS [MinDate]
FROM [X]
UNPIVOT (
    [Date] FOR d IN
        ([Date1]
        ,[Date2]
        ,[Date3])
) unpvt
GROUP BY [ID]

Upvotes: 6

Related Questions