I am building a query, where I need to get the MAX date from a list. When I run my query, the date comes back as a serial number like this:

How do I convert this back to a date???

This is the actual query I am using:

SELECT DISTINCT
O.Title AS Opportunity,
O.Contact AS Owner,
O.Account AS Account,
A.Parent_x0020_Account AS [Parent Account],
O.Status0 AS Stage,
O.Created AS [Created On],
O.DueDate AS [Estimated Close Date],
CU.Contract_x0020_Holder AS [Contract Holder],
CU.OEM AS OEM,
CU.Design AS Design,
CU.Number_x0020_of_x0020_Units as [Number of Units],
MAX(AC.Last_x0020_Activity_x0020_Date) AS [Last Activity Date]

FROM
Opportunities O
INNER JOIN Accounts A ON A.Title = O.Account
INNER JOIN [Customer Units] CU ON CU.Account = A.Title
LEFT OUTER JOIN Contacts C ON O.Contact = C.FullName
LEFT OUTER JOIN Activities AC ON AC.Contact = C.FullName

GROUP BY Opportunity, Owner, Account

Hi,

For some reason, the previous post did not display properly. Sorry.

date(MAX(AC.Last_x0020_Activity_x0020_Date)) AS [Last Activity Date]

should do the trick

Regards,

Do you mean that the following query will return 3971 items

SELECT DISTINCT
O.Title AS Opportunity,
O.Contact AS Owner,
O.Account AS Account,
A.Parent_x0020_Account AS [Parent Account],
O.Status0 AS Stage,
O.Created AS [Created On],
O.DueDate AS [Estimated Close Date],
CU.Contract_x0020_Holder AS [Contract Holder],
CU.OEM AS OEM,
CU.Design AS Design,
CU.Number_x0020_of_x0020_Units as [Number of Units],
MAX(AC.Last_x0020_Activity_x0020_Date) AS [Last Activity Date]

FROM
Opportunities O
INNER JOIN Accounts A ON A.Title = O.Account
INNER JOIN [Customer Units] CU ON CU.Account = A.Title
LEFT OUTER JOIN Contacts C ON O.Contact = C.FullName
LEFT OUTER JOIN Activities AC ON AC.Contact = C.FullName
GROUP BY Opportunity, Owner, Account

Whereas the following query will return 370 items?

SELECT DISTINCT
O.Title AS Opportunity,
O.Contact AS Owner,
O.Account AS Account,
A.Parent_x0020_Account AS [Parent Account],
O.Status0 AS Stage,
O.Created AS [Created On],
O.DueDate AS [Estimated Close Date],
CU.Contract_x0020_Holder AS [Contract Holder],
CU.OEM AS OEM,
CU.Design AS Design,
CU.Number_x0020_of_x0020_Units as [Number of Units],

FROM
Opportunities O
INNER JOIN Accounts A ON A.Title = O.Account
INNER JOIN [Customer Units] CU ON CU.Account = A.Title
LEFT OUTER JOIN Contacts C ON O.Contact = C.FullName
LEFT OUTER JOIN Activities AC ON AC.Contact = C.FullName
GROUP BY Opportunity, Owner, Account