What, no takers? OK then I'll go for it.

Test harness (now 1M rows):

/**************************************************

(1) Sample Data

**************************************************/

IF OBJECT_ID('tempdb..table1') IS NOT NULL

DROP TABLE table1;

IF OBJECT_ID('tempdb..table2') IS NOT NULL

DROP TABLE table2;

IF OBJECT_ID('tempdb..tally') IS NOT NULL

DROP TABLE tally;

CREATE TABLE table1 (id int primary key, [date] date not null);

CREATE TABLE table2 (id int primary key, [date] date not null);

CREATE TABLE tally (n int primary key);

;WITH

L0(c)AS(SELECT 1 UNION ALL SELECT 1 AS O), -- 2 rows

L1(c)AS(SELECT 1 FROM L0 [A] CROSS JOIN L0 ), -- 4 rows

L2(c)AS(SELECT 1 FROM L1 [A] CROSS JOIN L1 ) , -- 16 rows

L3(c)AS(SELECT 1 FROM L2 [A] CROSS JOIN L2 ), -- 256 rows

L4(c)AS(SELECT 1 FROM L3 [A] CROSS JOIN L3 ), -- 65,536 rows

L5(c)AS(SELECT 1 FROM L4 [A] CROSS JOIN L4 ), -- 4,294,967,296 rows

Tally_cte AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n FROM L5)

INSERT INTO tally

SELECT n FROM Tally_cte WHERE n<=1000000;

INSERT INTO table1

SELECT n,

CAST(

DATEADD(day,DATEDIFF(day,0,GETDATE())- 1 - FLOOR(RAND(CAST(NEWID() AS binary(4)))*365.25*90),0) AS date)

FROM tally

INSERT INTO table2

SELECT n,

CAST(

DATEADD(day,DATEDIFF(day,0,GETDATE())- 1 - FLOOR(RAND(CAST(NEWID() AS binary(4)))*365.25*90),0) AS date)

FROM tally

--SELECT * FROM table1;

--SELECT * FROM table2;

/**************************************************

(2) Queries

**************************************************/

SET NOCOUNT ON

dbcc freeproccache

PRINT 'Michael''s Solution';

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

select top 1

a.[Date]

from

(

select top 1 b.[Date] from table1 b order by b.[Date] desc

union

select top 1 c.[Date] from table2 c order by c.[Date] desc

) a

order by

a.[Date] desc;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

GO

DBCC freeproccache

PRINT 'Alan''s Solution';

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

SELECT MAX([date]) [date] FROM

(SELECT [date] FROM table1

UNION

SELECT [date] FROM table2) AS a

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

GO

DBCC freeproccache;

PRINT 'Dwain''s Solution';

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

WITH T1 (d1) AS (SELECT MAX([date]) FROM table1)

,T2 (d2) AS (SELECT MAX([date]) FROM table2)

SELECT [Date]=CASE WHEN d1 > d2 THEN d1 ELSE d2 END

FROM (SELECT d1=(SELECT d1 FROM T1), d2=(SELECT d2 FROM T2)) a;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

GO

Test results (IO counts and DBCC display omitted):

Michael's Solution

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 1685 ms, elapsed time = 595 ms.

Alan's Solution

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 6584 ms, elapsed time = 1935 ms.

Dwain's Solution

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 1357 ms, elapsed time = 440 ms.

Note that at the original 500K rows, Michael's solution has a bit of an edge in elapsed time over mine.