Link to home
Start Free TrialLog in
Avatar of JElster
JElsterFlag for United States of America

asked on

Get min date from column values

I have a table with 10 datetime columns/fields.

How do I get the min date of all the fields.

DATE1, DATE2, DATE3, DATE4, DATE5....


What is the SQL to get the MIN date from columns in the same record?


Need the earliest date of any of the 10 datetime columns.

thx
SOLUTION
Avatar of arnold
arnold
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image
Something like this:
WITH CTE_DATES (ID, Dates)
AS (
    SELECT ID, DATE1
    FROM TABLE
    UNION ALL
    SELECT ID, DATE2
    FROM TABLE
    UNION ALL
    SELECT ID, DATE3
    FROM TABLE
    UNION ALL
(...)
    SELECT ID, DATE10
    FROM TABLE
)
SELECT ID, MIN(Dates)
FROM CTE_DATES
GROUP BY ID

Open in new window

Avatar of JElster
JElster
Flag of United States of America image

ASKER

This is sql server 2005

Incorrect syntax near the keyword 'WITH'.
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of arnold
arnold
Flag of United States of America image
IMHO, depending on how many date Columbus you have, unpivot is better than trying to union all for the columns.
Avatar of JElster
JElster
Flag of United States of America image

ASKER

just needed a fast simple solution.. don't have time now to research pivot
thx