MS SQL Server - Compare Dates Fields

Author

Compare Dates Fields

sck10

2006-02-28, 8:23 pm

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'

2006-02-28, 8:23 pm

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'
>
>

sck10

2006-02-28, 8:23 pm

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...
>
>

Hugo Kornelis

2006-02-28, 8:23 pm

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 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.

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

Sponsored Links