Compare Dates Fields

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'

select  MAX(date_column_name
) from your_table_name



"sck10" <sck10@online.nospam> wrote in message
news:%23Ta80EIPGHA.2176@TK2MSFTNGP10.phx.gbl...
> 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



"ME" <ME@mail.com> wrote in message
news:OcjoZ7IPGHA.3576@TK2MSFTNGP15.phx.gbl...
> select  MAX(date_column_name
) from your_table_name
>
>
>
> "sck10" <sck10@online.nospam> wrote in message
> news:%23Ta80EIPGHA.2176@TK2MSFTNGP10.phx.gbl... 
>
>

On Tue, 28 Feb 2006 10:08:23 -0600, sck10 wrote:

>Hello,
>
>I have a table that has 3 date fields.  I need to compare the 3 against eac
h
>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.

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

--
Hugo Kornelis, SQL Server MVP