Minimum Date From Two Fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Minimum Date From Two Fields

Author

Topic

yukon
Starting Member

2 Posts

Posted - 2010-03-30 : 13:17:23

How do I get the minimum date when I have to compare two fields in the same table?

Example:

Date 1
1/20/1982
3/27/2002
7/15/1980

Date 2
3/25/2010
5/17/1997
8/20/1991

Minimum date should be 7/15/1980.

Sachin.Nand

2937 Posts

Posted - 2010-03-30 : 13:23:47

U can do a union all on both the columns & then use a min function.

PBUH

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-30 : 13:37:32

[code]SELECT MIN(z.MinDate) AS MinDate
FROM ( SELECT MIN(Date1) AS MinDate
FROM tableName
UNION SELECT MIN(Date2) AS MinDate
FROM tableName) z

-- OR

SELECT MIN(CASE WHEN Date1 > Date2 THEN Date2 ELSE Date1 END) AS MinDate
FROM tableName

-- OR

SELECT CASE WHEN MIN(Date1) > MIN(Date2) THEN MIN(Date2) ELSE MIN(Date1) END AS MinDate
FROM tableName

-- OR

SELECT CASE WHEN MinDate1 > MinDate2 THEN MinDate2 ELSE MinDate1 END AS MinDate
FROM ( SELECT MIN(Date1) AS MinDate1, MIN(Date2) AS MinDate2
FROM tableName) z
[/code]
Which of these is best would depend on a variety of things, like indexes, statistics, etc.

There are 10 types of people in the world, those that understand binary, and those that don't.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-30 : 13:39:35

quote:
Originally posted by Idera

U can do a union all on both the columns & then use a min function.

While a union was one of the examples I gave (slightly modified from this one), I'd probably stay away from it. It's likely to increase the number of logical reads required for the query.

There are 10 types of people in the world, those that understand binary, and those that don't.

yukon
Starting Member

2 Posts

Posted - 2010-03-30 : 13:49:17

Thank you very much to the both of you for your suggestions!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources