Need to find the most recent date based off the most recent time

NEW RESPONSE (based on new requirement in comment): In this new answer, I have added the LogText field and an additional JOIN to add that field to the most recent entries. If there were multiple entries with the exact same date and time, then you would see more than 1 entry for the same ErrorNum in that case. I hope this is the desired result, otherwise the query could be altered to only display a maximum of 1 result per ErrorNum regardless of how many logs occurred at the MAX date and time.

--here is the answer

SELECT            
    MostRecent.[ErrorNum],            
    MostRecent.[ErrorDateTime],            
    OriginalLog.[LogText]            
FROM            
    [dbo].[log] OriginalLog            
    INNER JOIN (            
    	SELECT            
    		[ErrorNum],            
    		MAX( DATEADD(ms, DATEDIFF(ms, 0, [LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, [LogDate]))) ) AS [ErrorDateTime]            
    	FROM [dbo].[log]            
    	GROUP BY            
    		[ErrorNum]            
    	) AS MostRecent            
    ON OriginalLog.[ErrorNum] = MostRecent.[ErrorNum] AND            
    	MostRecent.[ErrorDateTime] = DATEADD(ms, DATEDIFF(ms, 0, OriginalLog.[LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, OriginalLog.[LogDate])))            

--SQL Server 2008, full example --change the date and time types to datetime for SQL 2005 or 2000

CREATE TABLE [dbo].[log] (            
    [ErrorNum] int NOT NULL,            
    [LogDate] date NOT NULL,            
    [LogTime] time NOT NULL,            
    [LogText] varchar(128) NOT NULL )            

--change the insert statement to individual inserts for SQL 2005 or 2000

INSERT INTO [dbo].[log] ([ErrorNum], [LogDate], [LogTime], [LogText])            
VALUES  (245, '10/12/2009', '01:04:12', 'Invalid input from form MyFormA'),            
    	(244, '10/12/2009', '09:34:56', 'Unable to save form data MyFormA'),            
    	(245, '10/12/2009', '15:12:26', 'Invalid input from form MyFormB'),            
    	(244, '10/11/2009', '23:45:47', 'Unable to save form data MyFormB')            

--display the full resultset but concatenate the Date and Time fields

SELECT            
    [ErrorNum],            
    DATEADD(ms, DATEDIFF(ms, 0, [LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, [LogDate]))),            
    [LogText]            
FROM [dbo].[log]            

--now apply the GROUP BY and MAX functions

SELECT            
    MostRecent.[ErrorNum],            
    MostRecent.[ErrorDateTime],            
    OriginalLog.[LogText]            
FROM            
    [dbo].[log] OriginalLog            
    INNER JOIN (            
    	SELECT            
    		[ErrorNum],            
    		MAX( DATEADD(ms, DATEDIFF(ms, 0, [LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, [LogDate]))) ) AS [ErrorDateTime]            
    	FROM [dbo].[log]            
    	GROUP BY            
    		[ErrorNum]            
    	) AS MostRecent            
    ON OriginalLog.[ErrorNum] = MostRecent.[ErrorNum] AND            
    	MostRecent.[ErrorDateTime] = DATEADD(ms, DATEDIFF(ms, 0, OriginalLog.[LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, OriginalLog.[LogDate])))            

--clean up for the example

DROP TABLE [dbo].[log]            

ORIGINAL RESPONSE: The trouble is you are MAXing the date and times separately. All you have to do is concatenate the LogDate and LogTime fields and then MAX that. --here is the answer

SELECT            
    [ErrorNum],            
    MAX( DATEADD(ms, DATEDIFF(ms, 0, [LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, [LogDate]))) )            
FROM [dbo].[log]            
GROUP BY            
    [ErrorNum]            

I am grateful to Scott White for his simple way to concantenate the fields. Since you have the date and time in separate fields, I went ahead and did my example in SQL Server 2008, however, I have made notations so you can easily alter the example for 2005 or 2000. Let me know if you need more help with this.

--SQL Server 2008, full example
--change the date and time types to datetime for SQL 2005 or 2000

CREATE TABLE [dbo].[log] (            
    [ErrorNum] int NOT NULL,            
    [LogDate] date NOT NULL,            
    [LogTime] time NOT NULL )            

--change the insert statement to individual inserts for SQL 2005 or 2000

INSERT INTO [dbo].[log] ([ErrorNum], [LogDate], [LogTime])            
VALUES  (245, '10/12/2009', '01:04:12'),            
    	(244, '10/12/2009', '09:34:56'),            
    	(245, '10/12/2009', '15:12:26'),            
    	(244, '10/11/2009', '23:45:47')            

--display the full resultset but concatenate the Date and Time fields

SELECT            
    [ErrorNum],            
    DATEADD(ms, DATEDIFF(ms, 0, [LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, [LogDate])))            
FROM [dbo].[log]            

--now apply the GROUP BY and MAX functions

SELECT            
    [ErrorNum],            
    MAX( DATEADD(ms, DATEDIFF(ms, 0, [LogTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, [LogDate]))) )            
FROM [dbo].[log]            
GROUP BY            
    [ErrorNum]            

--clean up for the example

DROP TABLE [dbo].[log]            

This code makes a few assumptions:

• you have sql server 2005 or later
• LogDate and LogTime are either numeric or datetimes

            
select errornum            
       --min/max doesn't matter here since just one value is returned by cross apply            
       ,max(LogDate) LogDate             
       ,max(LogTime) LogTime            
from   [Log] as l            
cross apply (select top 1 LogDate, logTime             
              from [Log]            
              where errorNum = l.errorNum            
              order by LogDate desc, LogTime desc            
       ) ca            
group by errornum            

Another method

declare @t table(ErrorNum     int,Date          varchar(100),Time varchar(100))            
insert into @t            
select 245,          '10/12/2009',    '01:04:12' union all            
select 244,          '10/12/2009',    '09:34:56' union all            
select 245,          '10/12/2009',    '15:12:26' union all            
select 244,          '10/11/2009',    '23:45:47'             
            
select t1.Errornum,date,time from (select *,cast(date+' '+time as datetime) as date_time from @t) as t1 inner join            
(            
select ErrorNum,max(cast(date+' '+time as datetime)) as date_time from @t            
group by ErrorNum            
) as t2 on t1.ErrorNum=t2.ErrorNum and t1.date_time =t2.date_time            
order by t1.date_time desc