select MAX(date_column_name) from your_table_name



[quoted text, click to view]

Hello,

I have a table that has 3 date fields. I need to compare the 3 against each
other and get the max date. What would be the best way to do this,
function, case statement in a stored procedure? If there is any code
available, I would greatly appreciate it.
--
Thanks in advance,

sck10

Example of Table
--------------------
Date1: '10/1/2006'
Date2: '11/20/2006'
Date3: '5/1/2005'

Value = '11/20/2006'

Thanks, but I'm trying to figure out the latest date of 3 datefields across
the row. So the table structure would be like the following:
tblDateTracking
----------------
ScanID
UserName
SystemDate
ScanDate
IntlDate
--
Thanks in advance,

sck10



[quoted text, click to view]

[quoted text, click to view]

Hi sck10,

The fact that you need to do this suggests that your dedsign may not be
optimal. You might want to consider using a design where these three
date values in three columns in one row are transformed into the same
three date values in one column in three rows. If you do, you can use
the code posted by ME to find the maximum date.

With the current design, you're stuck with using something ugly and
unwieldy such as

SELECT ID,
CASE WHEN Date1 > Date2 AND Date1 > Date3 THEN Date1
WHEN Date2 > Date3 THEN Date2
ELSE Date3
END
FROM YourTable

or using a kludge such as

SELECT ID, MAX(TheDate)
FROM (SELECT ID, Date1 AS TheDate
UNION ALL
SELECT ID, Date2 AS TheDate
UNION ALL
SELECT ID, Date3 AS TheDate) AS Der
GROUP BY ID

--